2013/8/9 Durchholz, Joachim <[email protected]>
> > The base idea is that eliminating duplicates > > from large resultsets isn't going to be that > > much faster than issuing a second SQL query, > > not talking about network traffic and memory > > bloat. > > That's actually not always true. Depending on network latencies, the > additional round trips can be devastating. > E.g. with a 10 millisecond roundtrip and 10 queries, you're already at 0.1 > seconds, which can be above acceptability. > Stéphane was referring to fetching 2 or more subpaths, which usually denormalises results to produce cartesian products. If we're talking about 10 subpaths, issuing 10 queries is certainly better than producing a cartesian product from 10 entities just to remove duplicates again in Java memory... > Of course it depends on whether latency is relevant in the first place, > e.g. batch processing usually cares about throughput rather than latency > can parallelize to make the latency irrelevant. > > It also depends on how much wiggle room you have with latency. > If users are used to a second-long delay -> no problem. > If graph depth is only two or three -> no problem. > If latencies are around 1 ms -> no problem (unless graph depth is above 50 > or so). > > > IN (....) statement or EXISTS subqueries are usually well optimized in > modern rdbms. > > That works but only up to a limit; I have a vague memory that it's limited > to something like 2000 entries. > General graph loading code would need a strategy for that. > > OTOH if you're loading thousands of rows, you probably want to have > everything running on the database anyway and don't graph load. > Things get interesting if you need to do it in Java anyway, then you'll > still end with graph loading even if it's slow. > > > tl;dr: There are some kinds of requirements where doing subselects or IN > queries has issues. -- 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.
