Fly!

Basic tips on tuning SQL Server queries

Make your database fly!

I see people afraid of databases. Alive or dead, academic or professional. I’d never understand why. They end up forced to learn on the go. They don’t have time to realize some good practices — and tips! — to optimize performance on their databases and queries.

Badly-designed queries can degrade the database performance a lot. I have seen huge and time-consuming database processing jobs become blazing fast with just some fine-tuning on the queries executed. It is great to deploy a optimized product that runs dozen times faster than the previous version!

So it is necessary to avail the executed queries on database, on whatever platform it is running. This text focuses primarily on SQL Server, but I have sure that most of the tips fits all major platforms.

Note: I did write this article a few years ago. I was a certified SQL Server 2005 professional back then. Some things might be outdated.

• Don’t use the * in your queries. A SELECT * does an overload on the table, I/O and network bandwidth.
• All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear on index.
• Avoid VIEWs. Use them only when there are benefits of doing so. Do not abuse them.
• Verify if a critical query gains performance by turning it in a stored procedure.
• Avoid too much JOINs on your query: use only what is necessary!
• Avoid cursors at all costs!
• Always restrict the number of rows and columns of your result. That way, you save disk, memory and network of the database server. Always verify your WHERE clause and use TOP if necessary.
• Verify if your server isn’t suffering from not-enough-disk-space illness. Some times you lose time searching for all kind of problems only to find out that the server’s disk are almost full a few hours later. Always reserve at least 30% of available space on your disc.
• SQL Server is case insensitive: he does not care about ‘A’ ou ‘a’. Save time and don’t use functions like LOWER and UPPER when comparing VARCHARs. (As pointed out by Aravind V Shibu, this depends on the collation of your server or table. Please check it before!)
• The decreasing performance order of operators is: = (faster)>, >=, <, <=, LIKE, <> (slower)
• If a query is slow and your index is not being used by it (remember to check your execution plan), you can force it using WITH(INDEX=index_name), right after the table declaration on the FROM clause.
• Use EXISTS or NOT EXISTS instead of IN or NOT IN. IN operators creates a overload on database.
• Try to use BETWEEN instead of IN, too.
• When using LIKE operator, try to leave the wildcards on the right side of the VARCHAR.
• Always avoid to use functions on your queries. SUBSTRING is your enemy. Try to use LIKE instead.
• Queries with all operations on the WHERE clause connected by ANDs are processed from the left to right. So, if a operation returns false, all other operations in the right side of it are ignored, because they can’t change the AND result anyway. It is better then to start your WHERE clause with the operations that returns false most of the time.
• Sometimes is better to make various queries with UNION ALL than a unique query with too much OR operations on WHERE clause. Test it.
• When there is a HAVING clause, it is better to filter most results on the WHERE clause and use HAVING only for what it is necessary.
• If there is a need of returning some data fast, even if it is not the whole result, use the FAST option.
• Use, if possible, UNION ALL instead of UNION. The second eliminates all redundant rows and requires more server’s resources.
• Use less subqueries. If you must use it, try to nest all of them on a unique block.
• Avoid to do much operations on your WHERE clause. If you are searching for a + 2 > 7, use a > 5 instead.
• Use more variable tables and less temporary tables.
• Use functions to reuse code. But don’t exaggerate on using them!
• To delete all rows on a table, use TRUNCATE TABLE statement instead of DELETE.
• If you have a IDENTITY primary key and do dozens of simultaneous insertions on in, make it a non-clusterized primary key index to avoid bottlenecks.

Now, some tips for the table structure. Sometimes it is necessary to make some alterations on the table design to extract more performance!

• All tables should have a primary key. Except data warehouses and the like sometimes.
• All tables should have a clusterized index, normally on the primary key.
• Don’t be afraid to create non-clusterized indexes on most tables. Just be sure that you aren’t overindexing your tables! Too much indexes degrades insertions.
• If you are creating an index, check the queries that are made against the table. Give preference to index columns that appear on most WHERE and JOIN clauses, and their order!
• Avoid TRIGGERs. Use it only as a last resource. It is better to use CONSTRAINTs and STORED PROCEDUREs to maintain the integrity of your databases!

I hope that these tips help you optimize your database queries!