Leon, it's rare for me to disagree with you, but...
> From: Leon Rosenberg [mailto:[email protected]]
> For example if you need all orders by user with name Chris, you will
> ALWAYS be faster if you first retrieve the userid, and than the orders
> of the userid.
... I disagree and can produce at least one counterexample.
Performing two queries from the application layer requires two parses, two
optimise steps, at least two more context switches on a single-core machine,
two sets of serialisation of query and results, potentially more network
traffic and latency... all extra cycles and resource utilisation that are
avoided if the combined query is sent to the DBMS and executed there. Against
those, you have to balance more complex parse and optimise times for the single
query, plus the extra time to locate the data (which may or may not be in cache
at the server).
Back in 1992, I had exactly this situation on a Sybase 4.2 server on a
SPARCstation 1 running SunOS. I profiled both implementations, and the single
query case came back about 30% faster (I was only concerned about wallclock
time so didn't check memory or CPU). That was with Sybase's relatively
primitive optimiser. With a good query optimiser plus query plan caching and
data caching, a modern SQL Server can do better and can find the savings in
more cases.
I suggest losing the dogma and profiling it with *your* data in *your*
environment :-). You might be surprised.
- Peter
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
