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):

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

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

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))))  

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

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

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 ))

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.

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.

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 . 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. )

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?

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

(edit) fixed

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 )

(edit) fixed

Thanks