QUERY OPTIMIZATION TECHNIQUES

Few best practices to improve query performance

SELECT fields instead of using SELECT *

Avoid SELECT DISTINCT

Use WHERE instead of HAVING to define filters

Use wildcards at the end of a phrase only

Use WHERE expressions to limit the size of your results as much as possible.

Use EXISTS instead of IN to check existence of data

Create Clustered and Non-Clustered Indexes.

Drop unused Indexes

Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values

Use joins instead of sub-queriesUse joins instead of sub-queries

Create joins with INNER JOIN (not WHERE)

Avoid multiple joins in a single query

Avoid Cursors since cursor are very slow in performance

Avoid use of Non-correlated Scalar Sub Query

Use stored procedure for frequently used data and more complex queries.

Unfortunately sometimes, they forget to drop unused temp tables, and temp tables keep active in those idle connections. It also requires unnecessary utilization of disk space