Robert.W wrote:
Walaa,
Thanks for your answer. I did read this part of documentation before posting at least a couple of times.
You can use paging in combination of prefetch path with a page size larger than DataAccessAdapter.ParameterisedPrefetchPathThreshold but it will be less efficient.
"less efficient" is somehow misleading. It's not less efficient but it brings the whole system down to it's knees with 1000000 records being retrieved - that's why I wanted to make sure I'm not missing anything. Too bad that plain old joins are out of reach of LLBLGen for now.
the efficiency indeed depends on the # of rows fetched. The thing is that if the threshold is smaller than the page size, the prefetch path query should use a subquery instead of an IN query with values. Take this example:
you're fetching page 2, with size 100, of all customers from germany with their orders. Threshold is set to 50. For paging, the pager has to create a special paging query, e.g. with a CTE, Temptable etc. If you wouldn't be using paging, the query for the orders would look like: ('*' used for simplicity here)
select * from orders where customerid in (select customerid from customers where country = @country)
However, as you're using paging, only the orders of the customers on the page fetched should be read. With a threshold > pagesize, the query is filtering on the client, which orders to take. This can take a while or be fast (depending on the # of orders), as there's no other way: if your page has the size of 10000, you can't use an IN query anyway, a query can't have that many parameters.
So the key is to page with pages within the range of the threshold, so the query can use an in query instead:
select * from orders where customerid in (@cid1, @cid2, ... , @cid_pagesize_)
as the paging query used to fetch the customers can't be used as a subquery, that's the main problem.
A different solution would have been (but this isn't implemented currently) to create a temptable for the customerid's, store them there, and filter against that one. In this particular case a temptable would have been better:
select * from orders where customerid in (select customerid from #customerids)
But alas, that's not available now, so you've to work with the threshold to get good results with paging + prefetch paths. So if you're doing paging without prefetch paths: no worries. If you're doing paging WITH prefetch paths in 1 query, be sure the page size is in range of the threshold (if you're using compound PK's, be sure to use the formula (#fields*pagesize)<threshold.