Hey Frans,

I have a query generated by llblgen which at the moment returns one main entity and about 8000 related entities via a prefetch path. When I took the sql generated by the DQE and run it manually, it ran in about 2 seconds. However, I then made a test which simply fetches the entity collection and returns. This takes about 80 seconds to run. I haven't looked into the entity initialization procedures, but 80 seconds seemed rather long. Any ideas on what may be happening?

thanks, Jason

Prefetch paths use subqueries: a FieldCompareSetPredicate. A relation using fetch uses a join.

Say you have 10 customers who filed 10000 orders. And you want to select all those customers with a given set of orders, and it turns out there are 5. With a join, this results in 5000 rows with duplicates. With a subquery it doesn't, it returns 5 rows. Say, the customer entity contains a BLOB field, so DISTINCT can't be used, that's 5000 objects which are created and checked for duplicates, which can be rather slow.

So if you experience a slow relation-using query, try to rewrite the filter using a FieldCompareSetPredicate and no relation

In my database I have thousands of records, and according to your dialog I shouldn't consider using relation-using fetch....or am I wrong.... Is there any case in which I should prefer relation-using-fetch over FieldCompareSetPredicate/PrefetchPath?

orenpeled wrote:

In my database I have thousands of records, and according to your dialog I shouldn't consider using relation-using fetch....or am I wrong.... Is there any case in which I should prefer relation-using-fetch over FieldCompareSetPredicate/PrefetchPath?

Most databases use the same execution plans for subqueries as for joins, though in some situations it might be different. The thing with joins is that it can result in duplicate rows. For example, if you want to fetch all customers with orders lasts year, with a join it would result in something like this: select customer.* from customer inner join orders .... where... this leads to duplicate customer rows if a customer had more than 1 order last year.

This can be prevented by 'DISTINCT', but if the entity has a blob in the select list, DISTINCT can't be used. In that case, the code has to filter out duplicates on the client, i.e. outside the database. It then filters out duplicate rows in the fetch logic. With a lot of duplicates this is significant slower than when you would have used a subquery which doesn't produce duplicates.

When 1:n relations are used in a join, it can lead to a lot of duplicate rows, which in turn can lead to a lot of data being transported to the client (== llblgen pro runtime) unnecessarily. It therefore depends on the # of rows in the db what to do: use a subquery or a join. the subquery can be slower as well, as joins are easier to optimize and for example a subquery with a compound FK is slow, as you have to specify the subquery multiple times (once for each field in the FK)