SQL or Structured Query Language is frequently utilized in micro-services architectures to manage structured data, and is also commonly utilized in web and mobile applications for local data access and querying. Various domains including web, enterprise, financial, and e-commerce platforms benefit from SQL's robust querying and transactional capabilities.
However it is important to note that inefficient SQL queries can cause slow application response or delay in verifying created data.
To improve the performance of your queries, we recommend simple but effective recommendations. When it comes to query performance, simple changes can make a significant difference.
Our top recommendations
Avoid count(*) in queries
SELECT COUNT(1) or COUNT(single column name) will essentially do the job in most cases
Use exact number of columns wherever possible
Use exact number of columns in-stead of using * as for large tables the performance difference will be significant
Use joins over sub-queries
Use joins in-stead of sub queries wherever possible. Joins explicitly define the relationship between tables by specifying the columns used for matching rows. This explicitness helps the query optimizer make informed decisions about most efficient way to retrieve data
For frequently running queries, avoid DISTINCT especially for large tables as this is an expensive operation
Use If Exists over rather than a regular If
If COUNT(*) > 0, then BEGIN … END. Operations like this is very expensive. A more performant approach is, IF EXISTS(SELECT 1 FROM DBO.TABLE1) BEGIN … END
Where over Having
WHERE is better than HAVING in terms of performance. Rewrite your production queries to avoid Having whenever possible.
Temp Tables and sequential steps
For very long queries, use temporary tables and break the queries into sequential steps.
Remove unnecessary Order Bys
Remove unnecessary ORDER BYs. As an example, Order by in a subquery is meaningless.
Try using Indexed Column always
Use indexed column(s) in the where clause as the index will improve the performance of the query.
Avoid wildcard characters in the beginning of a regular expression with LIKE
Avoid wildcard characters in the beginning as it forces sql to do a table scan.
Queries involving Temp tables
Only insert the required columns and data into temp table as overall batch of sql statements might be expensive otherwise. Also it's important for the temp tables to be deleted after the usage is complete to free up memory.