Take with n > ParameterisedPrefetchPathThreshold generates query without WHERE

Posts   
 
    
Posts: 36
Joined: 19-Dec-2022
# Posted on: 15-Feb-2023 18:22:17   

Hi,

we are using LLBLGen 5.8.8-hotfix-2201220 with SelfServicing template group (we're updating to 5.10 soon). Our database is Oracle 19 and our target .net version 4.6.1/netstandard2.0 (happens for both). We noticed the following behavior which seems to be a bug: When you use the Take() on a IQueryable from LinqMetaData it seems that when generating the query LLBLGen respects the ParameterisedPrefetchPathThreshold. The problem seems to be that when you have a subquery in your linq and the count parameter of the Take() for your main IQueryable is over this threshold (default = 50) the subquery is generated as second query without a WHERE whatsoever and the whole table is fetched. This can result in a huge performance hit if the second query fetches a very big table. This happens with LLBLGen 5.8.8, 5.9.4 and 5.10.0.

Here is a specific example for more clarity (assuming ParameterisedPrefetchPathThreshold == 50):

LLBLgen creates a sub query with n parameters (in this example n = 49) :

new LinqMetaData()
    .Card
    .Select(c => new 
    {
        CardId = c.CardID,
        Products = c.Products.Select(p => new { ProductId = p.ProductID })
    })
    .Take(49) //n
    .Dump(); 

LLBLGen creates two queries. First query (generated by LLBLGen):

SELECT "LPLA_1"."CARDID" AS "CardId",
       1                 AS "LPFA_2",
       "LPLA_1"."CARDID" AS "CardID"
FROM   "CARD" "LPLA_1"
WHERE  rownum <= 49  

second query (containing 49 parameters - generated by LLBLGen):

SELECT "LPLA_2"."PRODUCTID" AS "ProductId",
       "LPLA_2"."CARDID"    AS "CardID"
FROM   "PRODUCT" "LPLA_2"
WHERE  ((("LPLA_2"."CARDID" IN (:p1, :p2, :p3, :p4,
                                :p5, :p6, :p7, :p8,
                                :p9, :p10, :p11, :p12,
                                :p13, :p14, :p15, :p16,
                                :p17, :p18, :p19, :p20,
                                :p21, :p22, :p23, :p24,
                                :p25, :p26, :p27, :p28,
                                :p29, :p30, :p31, :p32,
                                :p33, :p34, :p35, :p36,
                                :p37, :p38, :p39, :p40,
                                :p41, :p42, :p43, :p44,
                                :p45, :p46, :p47, :p48, :p49))))  

LLBLGen creates a sub query, but fetches ALL rows.

new LinqMetaData()
    .Card
    .Select(c => new 
    {
        CardId = c.CardID,
        Products = c.Products.Select(p => new { ProductId = p.ProductID })
    })
    .Take(50)  //n
    .Dump(); 

First query (generated by LLBLGen):

SELECT "LPLA_1"."CARDID" AS "CardId",
       1                 AS "LPFA_2",
       "LPLA_1"."CARDID" AS "CardID"
FROM   "CARD" "LPLA_1"
WHERE  rownum <= 50  

Second query fetches whole table (all rows!):

SELECT "LPLA_2"."PRODUCTID" AS "ProductId",
       "LPLA_2"."CARDID"    AS "CardID"
FROM   "PRODUCT" "LPLA_2"  

I would expect a generated query like those when using prefetch paths. Something like this:

SELECT "PRODUCTID" AS "ProductId",
       "CARDID"  AS "CardID"
FROM   "PRODUCT" 
WHERE  ("CARDID" IN
          (SELECT "CARDID" AS "CardId"
           FROM   "CARD" 
           WHERE  rownum <= 50 ))
Walaa avatar
Walaa
Support Team
Posts: 14975
Joined: 21-Aug-2005
# Posted on: 15-Feb-2023 23:04:16   

Could you please try adding a sort clause (order by) to the Cards? So the resultset can be deterministic and thus guaranteed to have the same IDs in the subquery.

Posts: 36
Joined: 19-Dec-2022
# Posted on: 16-Feb-2023 10:36:44   

Yes I've done that too. I didn't mention it to keep the example as simple as possible, but you're right. One should always use OrderBy when using Take like this. However, I tested with OrderBy on all three LLBLGen versions (5.8-10) and the result is always the same: the first query gets the order by the second query is generated like my example above, no WHERE.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 16-Feb-2023 11:06:03   

Markus@Init wrote:

Yes I've done that too. I didn't mention it to keep the example as simple as possible, but you're right. One should always use OrderBy when using Take like this. However, I tested with OrderBy on all three LLBLGen versions (5.8-10) and the result is always the same: the first query gets the order by the second query is generated like my example above, no WHERE.

This is to be expected, although you might not expect it to be so simple_smile. The nested queries are run the same way as prefetch paths and have to follow the same rules. The Linq (and queryspec for that matter) engine doesn't pass on the root's take/order by to the nested query. This might sound odd, but at the level it operates, it doesn't know which database it runs on, so it has to make a decision if the query might work, and with passing on take / orderby to the nested query it's not always the case it works here, as e.g. the orderby might be on a field that's not in the projection. For the root query that's fine, but for the subquery it's not.

To fix this, you have to do the same as with prefetch paths and paging (And 'Take' without skip is the same as take with skip here): set the ParameterizedPrefetchPathThreshold to a value equal or higher than the # of rows you're specifying with Take.

(For background, this old thread gives a little bit more info: https://www.llblgen.com/tinyforum/Message/Goto/62123. For prefetch paths we have a flag which is off by default, UseRootMaxLimitAndSorterInPrefetchPathSubQueries, which you can set on a prefetch path to work around this. There are some ways to make this work on most queries, but it's not as simple as it looks, sadly. )

Frans Bouma | Lead developer LLBLGen Pro
Posts: 36
Joined: 19-Dec-2022
# Posted on: 16-Feb-2023 13:12:36   

Thanks for the explanation, Otis. It's a pity that the DQE is not able to generate database specific optimizations on the subquery level, but I understand the intricacies of that problem. We will use ParameterisedPrefetchPathThreshold to get around this as you suggested.

Another little thing (too little for a new thread): I noticed that the value DynamicQueryEngine._defaultCompatibilityLevel changed from version 5.8 to 5.9 from Oracle9i10g11g to Oracle12c, but is not mentioned on https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/whatsnew.htm. I suppose this default change is worth mentioning?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 16-Feb-2023 13:32:39   

Good point, will add that to the docs (and also will add the migrating your code section for 5.10, as that's missing flushed )

(edit) fixed simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Posts: 36
Joined: 19-Dec-2022
# Posted on: 17-Feb-2023 16:13:06   

Otis wrote:

Good point, will add that to the docs (and also will add the migrating your code section for 5.10, as that's missing flushed )

(edit) fixed simple_smile

Thanks thumbsup