[geeks] SQL Performance

[Sridhar Ayengar]

James Fogg 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.
> 
> I haven't any data to point you to, but some empirical data.
> 
> For SQL performance and query size - it depends.
> 
> It depends on the CPU/bus/memory architecture of the machine, the
> design of the database, where the data resides (cache, memory or
> disk), what RDBMS you are using and the structure of the queries
> (query tuning is another black art). Also, RDBMS's can be tuned for
> particular query structures.
> 
> Sorry to be of little help. There's a reason top DBA's make good
> money and this is a good example of why.

Well, unfortunately, it's a Microsoft SQL Server database.  The machine 
it's running on is a dual-processor quad-core Xeon @2GHz with 16GB RAM. 
   There are two databases on the machine, one of which is relevant to 
the conversation, each on a four-drive SATA RAID 1+0 array on a hardware 
RAID controller.  Both read and write caching are enabled, with battery 
backup.  The records themselves are absolutely tiny, but I'm pulling 
large numbers of records even in the smaller result sets.

In the application I'm developing, it's power metering data I'm after, 
and I'm pulling a year's worth of data at a time.  Right now I'm pulling 
out data for one meter at a time.  My WHERE clauses are as specific as 
they can get.  The SUM and AVERAGE calculations I'm using in my report 
are being calculated on the database server so I don't need to pass 
large amounts of data across the network.

What I'm trying to determine is whether it would be beneficial for me to 
aggregate the meters that get added together in my application to make 
even bigger queries.

The operating system and database are both configured to use >4GB 
physical memory, and seem to be putting load across all eight cores as 
expected.  I had to do that stuff myself, since we don't actually have 
access to a SQL Server DBA.  I'm a decent Oracle and PostgreSQL DBA, but 
some of the stuff I'm seeing in SQL Server just plain doesn't make sense 
to me.

Peace...  Sridhar