Re: complex query vs performIteratedQuery vs Prefetching

[Hans Pikkemaat]

Hi,

What I can see when I use paging in combination with SQLTemplate is this:
Cayenne first runs the main SQLTemplate query which is stored in memory

When I get the first page it determines the key values of the main query 
which it then
uses in a new query which will return the main table plus the detail 
table data.
This will produce the main table object through which the detail table 
is accessible.

The problem here is that the key of the main table is used only. The 
SQLTemplate query was manually
constructed and does a query on the main table and a left join to the 
detail table so this will produce
a duplicate key value where a main table record has 2 related detail 
table records.

This doesnt have to be a problem, actually the query does return the 
number of records used as page
size. But internally in cayenne something weird happens. Somehow the 
duplicate records are removed
and the IncrementalFaultList.checkPageResultConsistency method throws an 
exception for this.

Because the main query returns the main object but also the detail 
object I find it strange
that the query generated for the page only uses the main table key. I 
would expect that
it also would use the key of the detail table.

An example. Say I have a main table key 1 and related detail records 
with key 1, 2 and 3.
Say I run the SQLTemplate which returns key 1 but only key 1 and 2 for 
the detail table.

The page query will now run for all detail records and return all 
records which I did not
request.

From this I'm concluding that if an SQLTemplate is used it is not 
usefull (read: faulty) to
include the detail table in this query. When paging is used all the 
detail tables are automatically
queried.

If I write the main SQLTemplate query such it only returns the main 
object then the
Exception does not occur.

My conclusion is then that if you want to use paging with SQLTemplate 
the main
query should only return the main table. Prefetching will then return 
ALL related
table records.

Hans

Andrus Adamchik wrote:
Actually IncrementalFaultList iterator internally uses list.get(int)  
(as shown in the stack), so it seems to be the same thing as iterating  
with an index.

Andrus

On Nov 11, 2009, at 4:02 PM, Andrey Razumovsky wrote:

  
Could you try iterating through list using counter variable (e.g.  
for (int i
= 0; i < list.size(); i++)) to see if it works?

2009/11/11 Hans Pikkemaat <h.pikkem...@tsi-solutions.nl>

    
Hi,

This is the 'working' part

     System.out.println("Running with fetchlimit: " + pageSize);
     final DataContext dataContext = this.createDataContext();

     SQLTemplate query = new SQLTemplate(VersionedObjectImpl.class,  
sql);
     query.addPrefetch(_VersionedObjectImpl.ATTRIBUTES_PROPERTY);

     if (pageSize > 0) {
         query.setPageSize(pageSize);
     }
     List<VersionedObjectImpl> list =  
dataContext.performQuery(query);

     for (Iterator<VersionedObjectImpl> iterator = list.iterator();
iterator.hasNext();) {
         this.show(iterator.next());
     }

VersionedObjectImpl.class is the main table and the
VersionedobjectImpl.ATTRIBUTES_PROPERTY
is the relation name for the detail table.

The sql provided is (my own made simple 'try out' query, the actual  
query
is WAY more complex)

select       o.object_id         as "OBJECT_ID",     o.ancestor_path
 as "ANCESTOR_PATH",     o.object_type         as "OBJECT_TYPE",
o.object_identifier         as "OBJECT_IDENTIFIER",
o.created_revision_id         as "CREATED_REVISION_ID",
o.deleted_revision_id         as "DELETED_REVISION_ID",      
a.attribute_id
     as "attributes.ATTRIBUTE_ID",     a.name         as
"attributes.NAME",     a.type_name         as "attributes.TYPE_NAME",
a.string_value         as "attributes.STRING_VALUE",      
a.integer_value
   as "attributes.INTEGER_VALUE",     a.date_value         as
"attributes.DATE_VALUE" from  rev_object o left join       
rev_attribute a
   on a.object_id=o.object_id

If I run this without page size no problem.
With page size set eg to 100 I get


Exception in thread "main"  
org.apache.cayenne.CayenneRuntimeException:
[v.3.0B1 Nov 09 2009 09:53:47] Some ObjectIds are missing from the  
database.
Expected 100, fetched 85
 at
org 
.apache 
.cayenne 
.access 
.IncrementalFaultList 
.checkPageResultConsistency(IncrementalFaultList.java:364)
 at
org 
.apache 
.cayenne 
.access 
.IncrementalFaultList.resolveInterval(IncrementalFaultList.java:301)
 at
org 
.apache 
.cayenne.access.IncrementalFaultList.get(IncrementalFaultList.java: 
550)
 at
org.apache.cayenne.access.IncrementalFaultList 
$1.next(IncrementalFaultList.java:467)
 at
com 
.traserv 
.incrementalsupply 
.integration.demo.CayenneDemo.doit1a(CayenneDemo.java:188)
 at
com 
.traserv 
.incrementalsupply 
.integration.demo.CayenneDemo.main(CayenneDemo.java:235)


tx

Hans


Andrus Adamchik wrote:

      
You may have done that already in the previous messages, but could  
you
give a snip of your SQLTemplate creation code here (just to make  
sure  I
understand all the settings used in this specific case). And also if
possible, SQL generated in the console for the initial query, and  
then  for
the failing page query?

Thanks,
Andrus


On Nov 11, 2009, at 2:51 PM, Hans Pikkemaat wrote:



        
Hi,

I tried 3.0b but without effect.

If I use an SQLTemplate in combination with setPageSize I also  
get  the
exception mentioned earlier:

Exception in thread "main"   
org.apache.cayenne.CayenneRuntimeException:
[v.3.0B1 Nov 09 2009  09:53:47] Some ObjectIds are missing from the
database. Expected  100, fetched 85
  at  org .apache .cayenne .access .IncrementalFaultList
.checkPageResultConsistency(IncrementalFaultList.java:364)
  at  org .apache .cayenne .access
.IncrementalFaultList.resolveInterval(IncrementalFaultList.java: 
301)
  at  org .apache
.cayenne 
.access.IncrementalFaultList.get(IncrementalFaultList.java: 550)
  at org.apache.cayenne.access.IncrementalFaultList
$1.next(IncrementalFaultList.java:467)
  at  com .traserv .incrementalsupply
.integration.demo.CayenneDemo.doit1(CayenneDemo.java:136)
  at  com .traserv .incrementalsupply
.integration.demo.CayenneDemo.main(CayenneDemo.java:183)

If I use a SelectQuery it works oke. But this is not an option  
for  me
because I cannot construct
my query using SelectQuery because of its complexity.

Any ideas?

tx

Hans


          

        

      
--
Andrey