Hi,

When using FetchEntityCollectionAsync with paging and prefetch and ParameterisedPrefetchPathThreshold is passed (page size is big) it basically pulls whole prefetch table. Prefetch table has millions of records, is there a way to optimize this? We will probably increase ParameterisedPrefetchPathThreshold if there is no other solution.

Here is how query looks like:

SELECT * FROM "A" ORDER BY "A"."Name" DESC LIMIT 100 OFFSET 200

SELECT * FROM "B" WHERE "B"."Id" IN (SELECT "A"."BId" FROM "A")

https://www.llblgen.com/Documentation/4.2/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_prefetchpaths_adapter.htm

LLBLGen 4.2 PostgreSQL 9.4

To do paging with prefetch paths the prefetchpaththreshold must be adjusted so it matches the page. If you have a compound PK, say 3 fields, then the threshold must be 3* pagesize.

It doesn't pull the whole table, but will read as much rows as it needs to fulfill the page request.

The problem is that if you don't set the prefetchpaththreshold to a value bigger than a page, it is forced to use a subquery (like your 2nd query) and it then has to filter the child rows (B) on the parent rows (A), but the rows in A are fetched using a page directive so the page directive has to be present in the subquery too which isn't supported by many (if any) database.

Using the threshold will make the runtime generate an IN(...) predicate instead of a subquery with all the PK values of A, so this works in a paging scenario.