I am well aware that IN queries have their limitations too.
Here is how llblgen used to solve this problem :
1) It would fill the main collection first, eg :
SELECT *
FROM orders
WHERE orders.customerID = @customerID
ORDER BY orders.checkout_date
2) then it would decide which query to use for fetching the relation
a) In case there less than 50 orders (you could configure how many values
exactly) It would us an IN predicate like this one:
SELECT *
FROM orderdetails
WJERE orderdetails.orderID IN ( 1, 5, 8, 20 ...)
b) In cas there is more orders than that, it would re-use the first query
SELECT *
FROM orderdetails
WHERE orderdetails.orderID IN (
SELECT orderId
FROM orders
WHERE orders.customerID = @customerId
)
Maybe it could be better to use EXISTS, but it's already a nice alternative
to an infamous N+1.
Best
Le vendredi 9 août 2013 15:23:25 UTC+2, Lukas Eder a écrit :
>
>
>
>
> 2013/8/9 Durchholz, Joachim <[email protected]<javascript:>
> >
>
>> > Stéphane was referring to fetching 2 or more
>> > subpaths, which usually denormalises results
>> > to produce cartesian products.
>>
>> Ah sorry, I had thought this was being proposed for each element of a
>> join path.
>> Hadn't read closely enough, sorry for the spam.
>> I agree that cartesian product blowup would be worse than latency.
>>
>> I believe that the point about limits to the list of values still stands.
>
>
> You mean, limits to the number of bind values? Yes, there are some limits:
>
> - Oracle IN predicate: 1000 elements
> - Ingres: 1024 total bind values
> - SQLite: 999 total bind values
> - Sybase ASE: 2000 total bind values
> - SQL Server 2008 R2: 2100 total bind values
>
> jOOQ can transform SQL if any of the above events occur, though, either by
> splitting the IN predicate, or by inlining bind values.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
