Fog Creek Software
Discussion Board

SQL and performance

In an earlier thread, someone asked for advice about tuning a specific SQL query on a MS SQL Server database, and that raises two questions I'm curious about:

1) Can anyone recommend good books or articles for learning how to write fast, efficient SQL - especially for clusters? And for learning about database performance in general?

2) Do third-party persistence layers tend to produce fast, efficient SQL? For example, EJB containers create SQL for CMP entity beans - but I've always wondered how good the SQL these things produce is. I'd be interested in hearing what people's experiences have been (just with the persistence side of things, wither it's .Net, EJB, etc)

Wednesday, April 9, 2003

Some good SQL Server specific info here:

Bill Carlson
Wednesday, April 9, 2003

I'm reading a great book right now called "SQL Performance Tuning", by Gulutzan and Pelzer.  Full of ideas all backed up by benchmarked tests.

Spaghetti Rustler
Wednesday, April 9, 2003

In regards to #2, first decide what is "fast enough."  It may sound silly, but people often get hung up over .123 seconds on a three second transaction.

If you are moving fast enough, then they are doing a fine job.  However, they will almost never do as good a job as an individual coding the access.  Why?  Because you can make intuitive judgments about the data and the access itself that generated/generic interfaces cannot. 

Building your own costs in maintenance hours.  Paid for forever.  However, they can make a two second transaction .5 and that is a good return.  A bad return is going from .6 to .5, as it may look good as a percentage but makes not difference to the end user/process.

Mike Gamerland
Wednesday, April 9, 2003

So far I have read:

Scaling Oracle
Prentice-Hall PTR's and O'Reilly's Oracle performance tuning books

People in the SQL Server space beg for performance too, so it's worth reading some of the good books out there. I like an AP press book on stored procedure but that's just me.

They are all pretty good. I learn a lot without a lot of pain from Scaling Oracle though, highly recommended.

Plain administration and programming books will normally have enough tips to take you 90% of the way on optimization with indexes and stored procedures and ADO optimizations for everyday databases. But more and more databases are weird as more people try to use RDBMSs to solve their problems.

Li-fan Chen
Wednesday, April 9, 2003

I've got the "Guru's Guide to Transact SQL" and "Inside Sql Server 2000". They pretty much cover everything you would need to know about tuning Sql server for performance. It's too much information to hold in your head, so I work with them constantly next to me. Between the two you cover the high end (T-SQL) and the low end (the relational engine etc). Learning how the engine works is a big boon.

Like I said, too much to hold in your head, but when you hit a snag they're like a godsend.

Also, being able to interpret query plans. :)

Geoff Bennett
Wednesday, April 9, 2003

It depends on whats hitting you. One simple trick is "more spindles" and RAM. Other kludges are things like having multiple copies of the same stored procedure and picking one at random to stop some of the thread stalls that can otherwise occur.
I got a big pile of advice from one of the MS guys at the server developer conference last year, but we never needed it. (Sadly thats all (C) Microsoft I think)

In practice if you have an enterprise class cluster then you can probably get access to these guys with no trouble.

Peter Ibbotson
Thursday, April 10, 2003

I second "Guru's guide[...]" and "Inside SQL[...]".. do grab the book for SQL Server 7.0 for the Guru's guide.

Li-fan Chen
Thursday, April 10, 2003

I've read a number of books on SQL Server, a few of them quite good.  I've read a great many web pages, usenet posts, etc. on it.

Nowhere did I find in depth information on making queries go fast in complex, real world situations, particularly in situations where SQL Server does not do something efficient and good by default.  Instead, we (at my firm) essentially had to figure it out for ourselves, with many hours of experiments with SQL Query Analyzer etc.  The payoff, though is that now we can generally make SQL Server do the right thing, even when it does not want to, for very complex queries.

Kyle Cordes
Saturday, April 12, 2003

*  Recent Topics

*  Fog Creek Home