Sorry guys for all the typos in my last answer. @Joaquim > 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.
To be honest, even in the case there no cartesian product ( eg a collection of customers with their orders) it would already be nice to have. Using the record API, you would be trading a N+1 for a 1+1. As you said, 1+1 may not beat a JOIN with some processing. (let's call that 1+0 ). As a starting point, taking an easy route such as implementing prefetch paths using one query per relation, ignoring whether it's 1:1 or 1:n would be excellent. 100% agreed that in some cases, it's only second to best behind a join. However it's better than any N+1 and it's probably easier to implement a generic system that would reuse your initial query results (or reuse the initial query itself in a subquery) than a sophisticated that would modify your initial SQL query to include the necessary joins and be smart enough to know how to decompose the resultset into a graph. Le vendredi 9 août 2013 18:08:09 UTC+2, Stéphane Cl a écrit : > > 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]> >> >>> > 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.
