SQL SERVER Query Performace Tips and Tricks

I see that some people are afraid of databases, academic and professional. Or something likes that, I’d never understand why. They end up learning on the go and not realizing some tips and good practices to optimize performance on their databases and queries.

Badly-designed queries can degrade the database performance too much. I have seen huge processing jobs become blazing fast with just some adjusting on the queries executed. It is great to deploy an 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.

• Don’t use the * in your queries. A SELECT * creates an overload on table, I/O and network bandwidth.

• All columns involved in indexes should appear in WHERE and JOIN clauses in the same sequence they appear in an index.

• Avoid VIEWs. Use them only when there are benefits of doing so.

• Verify if a critical query gains performance by turning it into 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. Sometimes you lose time searching for other problems only to find out that the server’s disk are almost full. Always have at least 30% of available space in your disc.

• SQL Server is case insensitive: he does not care about ‘A’ or ‘a’. Save time and don’t use functions like LOWER and UPPER when comparing VARCHARs.

• The decreasing performance order of operators is:

• = (faster)>, >=, <, <=LIKE<> (slower)

• If a query is slow and your index is not being used to it (remember to check your execution plan), you can force it usingWITH(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 an 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 using functions in 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 an operation returns falsely, all other operations on the right side of it are ignored, because they can’t change the AND result anyway. It is better than 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 in 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 fewer subqueries. If you must use it, try to test all of them on a unique block.

• Avoid doing many 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 by using them!

• To delete all rows from a table, use TRUNCATE TABLE statement instead of DELETE.

• If you have an 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 to 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 over indexing your tables! Too many indexes degrade insertions.

• If you are creating an index, check the queries that are made against the table. Give preference to index columns that appear in most WHERE and JOIN clauses and their order!

• Avoid TRIGGERs. Use it only as a last resource. It is better to use CONSTRAINTs and STOREDPROCEDUREs to maintain the integrity of your databases!