[geeks] SQL Performance

[Sridhar Ayengar]

Patrick Giagnocavo wrote:
>> Does anyone know of any papers or web pages containing performance 
>> comparisons between accessing data with many small SQL queries versus 
>> accessing the same data with larger aggregate queries?
>>
>> I would expect the smaller number of large queries to perform better, 
>> but I have no frame of reference for being able to estimate how much 
>> of a difference it would make.
> 
> The answer is of course, "that depends".
> 
> What I suggest you do is to take both the smaller number of queries, and 
> the single large query, and run them both through EXPLAIN PLAN, which 
> will show you the way that the optimizer will handle the query.

Hmm.  Sounds like a good idea.  I'll get cracking on that.  Should at 
least let me get a decent idea about the trending.

> In terms of estimating time, you will have to sum together:
> 
> optimizer time to generate query plan
> 
> round trip time between client and server (whether on same computer or 
> different ones)
> 
> difference between number of IOs performed using one largw query or 
> several smaller
> etc.
> 
> You may wish to examine the use of either a view on the table (which has 
> its query plan optimized once, then cached, usually) or a stored 
> procedure which is called to return the data (so you can feed it 
> parameters, for instance) and which will minimize communication overhead 
> if the database server is separate from the client.

Now there's an idea.  I hadn't thought of that.  I am using precompiled 
SQL statements in my code though, so I'm not sure exactly how much that 
would save me.  It shouldn't be too hard to test though.

> If using MySQL, the real answer usually involves switching to Postgres 
> if you need more performance, but then again that is just my opinion.

Hehehehe.  I've come across that myself.

Peace...  Sridhar