[geeks] SQL Performance

[Jonathan C. Patschke]

On Mon, 21 Jul 2008, Sridhar Ayengar wrote:

> Well, unfortunately, it's a Microsoft SQL Server database.

There's actually nothing wrong with that, aside from the platform on which
it resides.  Microsoft SQL Server is a nice[0] piece of software.

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

1) Do you have indexes along the columns you're using in your WHERE clauses?
2) Are you using stored procedures (or at least parameterized queries)
    instead of ad hoc queries?

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

If you're doing SUMs and AVERAGEs along the same subsets of data, you
might want to consider a trigger that updates those values in a cache
somewhere whenever the table contents change.

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

SQL Server, by default, obfuscates more stuff than it needs to, in the
interest of making it "easier" to be used by people who don't belong
anywhere near a database server.  If you dig a bit, you can actually get
at everything you need, though.   I learned SQL Server 7 from the
Microsoft Press books; I found that they got me up and running efficiently
very quickly.  If you're not running a bleeding-edge version of SQL
Server, you can probably pick up the appropriate editions of "Programming
SQL Server" and "Inside SQL Server".


[0] And, as with all the software they sell that isn't completely crap,
     they originally bought it from someone else (Sybase, in this case).
-- 
Jonathan Patschke | "There is more to life than increasing its speed."
Elgin, TX         |                                   --Mahatma Gandhi
USA               |