[geeks] SQL Performance

[Patrick Giagnocavo]

Sridhar Ayengar 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.

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.

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

--Patrick