top of page
Biswajit Nanda

Make your SQL run 10X faster

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 ​


Avoid Distinct

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.

34 views0 comments

Commentaires


bottom of page