Tips for improving your OLTP queries performance

The basis for a good query design is to have your database normalized. Normalization will ensure the data integrity and will minimize the data duplication so you know that a data will be only in a single place. With that you will be able to write fast and less complex queries.

Having a normalized table means that all your tables should have both a primary key (PK) and foreign keys (FK) constraints where it applies. You can only have a PK per table and in an SQL Server it will create all PKs as clustered indexes by default unless you specifically say not to. Even though this can be good for most of the cases, it may not be the best in other cases so review the Clustered Indexes to be sure that you have chosen the best one for each table.

The next step is to create the rest of necessary non-clustered indexes to boost the query (typically on the fields used in the WHERE clause of your queries).

NOTE: The work with the indexes does not ends here. Maintenance of the indexes is one of the most important maintenance tasks, so create a regular job to Reorganize and Rebuild indexes in your database. This will ensure a constant low level of index fragmentation and will help your queries run faster.

Until now, I just talked about the physical organization of the tables that are truly the basis for having a good query performance but this may not be enough if you write a poor query syntax.

So what do we know by now?

1. Create the PK and FK for all tables where it applies so you can use the correct keys in the JOIN clauses
2. Create the necessary indexes on columns used in the WHERE clauses of your queries

But there are more things to be taking into consideration when writing your queries, such as:

• avoid DISTINCT clauses—when you need to use the DISTINCT clause it might be because you are missing a JOIN table or column in your query. If that's not the case then you can always replace the DISTINCT with a GROUP BY clause that will have the same effect but with much better performance.
• avoid ORDER BY clauses—sort operations takes time and when possible, you should let this kind of operations run on the client side. You can also avoid ORDER BY if the table has the correct Clustered Index so records will be sorted by respecting the cluster index order.
• do not use functions in the WHERE clause over indexed fields—indexes will not be used when a function applies on a indexed column. When doing so, you will negatively impact the query performance.

In conclusion, start with a well and normalized database design. This is the basis for building good queries in any RDBMS. Then choose the best candidates for your indexes (clustered and non-clustered) and schedule regular index defragmentation jobs. Lastly, avoid the use of poor performance SQL clauses. Respect these rules and you will have less performance issues in the future.