Tips to improve SQL query performance
1. Avoid usage of multiple joins in a single query. Unnecessary joins degrade your query performance.
2. Creation of indexes on the table. Indexes are used for improving your queries. We need to create the indexes on the columns which we query most. We can create multiple non clustered indexes on a table. So, many developers think that they can create as many indexes as they need as indexes make our queries faster. But unnecessary indexes should be avoided. Unnecessary usage of indexes can degrade your SQL Insert and Update queries.
3. While creating indexes, order of columns specified also matters. You need to specify the columns which we query most first but include other columns too if those are necessary for your queries. Order of columns should be in such a way that your query columns are left most in the index key.
4. Avoid using same subqueries in your queries. Instead store the sub query as a separate query into the temp table or some output variable depending on your subquery result so that you can use that output in your multiple queries whenever needed instead of querying db again and again for the same thing.
5. You should choose your column data-types very wisely. Suppose, you have a column which can take one digit value then you should use the datatype tinyint(1) instead of int which we normally choose for number type fields. For small databases, this does not matter much but if you have large database with large number of tables and columns with multiple indexes then memory becomes the major bottleneck which will ultimately slow down your queries. So, you should understand your data and decide on appropriate datatypes of your data to improve space usage.
6. Statistic creation and updation. You need to be careful about stats creation and their regular updates as query optimizer uses these stats to create highly optimized query plans.
7. You need to regularly rebuild your indexes as fragmentation happens if insertion/updation are very frequent on your indexes columns. Usage of indexes by the database server become less likely if this fragmentation increases beyond limit.
8. While querying, only specify the columns you need to query instead of * (All columns) which will improve your SQL query performance by using the right indexes based on columns specified.
9. Use proper schema definitions. Create foreign key and other constraints as they also make our query faster since some of the cross-data checks for data validation need not to be specified in the queries.
10. Avoid usage of cursors as cursors are very slow in performance.
11. Use table variables instead of temp tables as querying the temp table is time consuming task but when using joins you should use the CTE expressions (a temporary named result set which is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement) or temp tables because table variables perform horribly when used with joins because of RAM constraints.
12. Use stored procedures for complex type of queries as they not only increase your query performance but also adds additional layer of security. Stored procedures improve the query performance as fewer calls are made to the database. We have only one place to change if requirements change. We can call the stored procedures in any language. We can use it across multiple applications.
13. Avoid usage of calculative columns in where clause and joins as this will slow down our queries. Avoid using Group By and Order By clauses until necessary.