2013/8/9 Stéphane Cl <[email protected]> > 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 > ) >
Aha, I see... I must've missed the idea behind using IN / EXISTS from your previous discussions. I wonder if there are any significant caveats when transforming the original SQL (1) into the subselect (2b) for arbitrary SQL statements (allowing for GROUP BY, LIMIT, etc.). Again, I feel that implementing something on a CRUD level might be much simpler and more effective, than adding a new synthetic SQL clause. I'm sure the LLBLGen guys had thought about this thoroughly, though, given the maturity of their products. Maybe it could be better to use EXISTS, but it's already a nice alternative > to an infamous N+1. > Sophisticated databases should be able to transform an IN predicate into an equivalent EXISTS predicate, although the difference might not be relevant in the execution plans. In other databases, NOT EXISTS can perform worse than LEFT JOINs or IN: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql I don't think there's a general preference rule to prefer one or the other SQL rendering, which holds true across all DB vendors... Cheers Lukas Best > > > Le vendredi 9 août 2013 15:23:25 UTC+2, Lukas Eder a écrit : >> >> >> >> >> 2013/8/9 Durchholz, Joachim <Joachim....@hennig-**fahrzeugteile.de> >> >> > 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. > > > -- 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.
