Hi,

i'm experiencing very slow performance when using prefetchpaths in the following scenario.

Table 'Order' has a 4-field primary key (say PKF1, PKF2, PKF3 and PKF4) and a 1 to many relation with table 'OrderLine', which has a 8-field primary key (first 4 fields match Table 'A')

Using prefetch paths to search for all orders of a particular person (Person is an indexed attribute in 'Order') and all associated OrderLines, it is very slow for persons having more than 100 orders. My guess is, that the generated SQL isn't very optimized here. I tried to tweak with 'DataAccessAdapter.ParameterisedPrefetchPathThreshold', but i think because of the nature of a multi-field PK, it always results in the same SQL.

This is roughly the generated statement:

SELECT * FROM OrderLine WHERE (OrderLine.PKF1 IN (SELECT PKF1 FROM Order Where Person=100)) AND (OrderLine.PKF2 IN (SELECT PKF2 FROM Order Where Person=100)) AND (OrderLine.PKF3 IN (SELECT PKF3 FROM Order Where Person=100)) AND (OrderLine.PKF4 IN (SELECT PKF4 FROM Order Where Person=100))

Problem is that the subqueries produce a much to big resultset.

Better was to use 'EXISTS' instead of 'IN', or even better:

SELECT * FROM OrderLine WHERE (OrderLine.PKF1,OrderLine.PKF2,OrderLine.PKF3,OrderLine.PKF4) IN (SELECT PKF1, PKF2, PKF3, PKF4 FROM Order Where Person=100)

which is way faster.

The generated query now takes more than a minute! For your information: all this is done on an Oracle database.

Is there any way to improve performance in this scenario?

Some extra info: - LLBLGen Pro version 2.0.0.0 Final (August 3rd, 2006) - Oracle ODP.NET 9.2.0.7

Would you please download and use the latest LLBLGen Pro runtime library version? And check if the problem is still there.

Just intalled the latest version (full install). LLBLGen Pro 2.0.0.0 (March 21st, 2007). Same problem!

This isn't solvable with the current code, as the SQL statement you gave which does work is Oracle specific, and hte filter predicates specified are field compare set, not fieldS compare SET.

I don't fully understand. Shouldn't the Oracle specific adapter be able to produce these Oracle-specific statements? Are there any plans on making the prefetchpath generated statements 'multi-field'-friendly (in letting them generate 'fieldS compare set' in stead of 'field compare set')? Because, as they are behaving now, they're really unusable in this scenario. Our legacy database is stuffed with tables containing PK's with multiple fields ( ). It would have been nice to have my hierarchical grids automatically populated with prefetched data (in a reasonable amount of time). Is there another way of accomplishing this?

Try to set the ParameterisedPrefetchPathThreshold = 0 This will switch to a subquery with a select statement rather than a range predicate.

Ruudster wrote:

I don't fully understand. Shouldn't the Oracle specific adapter be able to produce these Oracle-specific statements?

No as the predicate classes themselves produce the SQL though for db specific details (parameter names etc.) they ask a creator.

All predicates are targeting a single field, not a set of fields, hence the lack of support for this (as no other db supports this IMHO)

Are there any plans on making the prefetchpath generated statements 'multi-field'-friendly (in letting them generate 'fieldS compare set' in stead of 'field compare set')? Because, as they are behaving now, they're really unusable in this scenario. Our legacy database is stuffed with tables containing PK's with multiple fields ( ). It would have been nice to have my hierarchical grids automatically populated with prefetched data (in a reasonable amount of time). Is there another way of accomplishing this?

The threshold for the parameterized prefetch path uses (#FKfields * #rootrows) as the limit. So if you have 100 rootrows and 4 FK fields, you've to set the threshold to at least 400. Oracle has a limit of 1100 parameters per query, so as long as you stay below the 250 orders, it should be OK. This is of course not a solution for the long term.

Could you try creating an index on each orderline FK field individually?

Try to set the ParameterisedPrefetchPathThreshold = 0 This will switch to a subquery with a select statement rather than a range predicate.

It's the subqueries that are slow. There have always been subqueries generated, never a range predicate.

Could you try creating an index on each orderline FK field individually?

This did the trick! Of course, the DBA isn't gonna be thrilled in creating an extra 'xillion' indexes... but in the end, the end-users will probably force him to... Thanks for the solution!