30 SQL
PERFORMANCE TIPS & TRICS
Best Practices to Improve
the Performance of Your
Queries
hritiksingh1611@gmail.com
THANK ME LATER
@Hritiksingh
FETCHING DATA
1 Only select the columns you need. Avoid
using SELECT *
Avoid using DISTINCT or ORDER BY
2 unless absolutely necessary, as they
can slow down queries
For exploration, limit the number of
3 rows using TOP to avoid fetching
unnecessary data
@Hritiksingh
FILTERING DATA
4 Create non-clustered indexes on columns
frequently used in the WHERE to speed up
queries
Avoid functions e.g., UPPER(), YEAR()
5 to columns in the WHERE , as this
prevents indexes from being used
Avoid starting string searches with a
6 wildcard (%example), as this disables
index usage
7 Use IN instead of multiple OR conditions
for better readability and performance
@Hritiksingh
JOINING DATA
8 Understand the performance implications
of different join types. Use INNER JOIN
when possible for efficiency
Always use explicit (ANSI-style) joins
9 (INNER JOIN, LEFT JOIN, etc.) instead
of older implicit join syntax
Ensure that the columns in the ON of
10 your joins are indexed for optimal
performance
Filter before joining large tables to
11 reduce the size of the dataset being
joined
@Hritiksingh
12 Aggregate before joining large tables to
reduce the size of the dataset being joined
13 Replace OR conditions in join logic with
UNION where possible to improve query
performance
14 Be aware of nested loops in your query
execution plan. Use SQL Hints if needed to
optimize performance
Use UNION ALL instead of UNION if
15 duplicates are acceptable, as it is
faster
When duplicates are not acceptable,
16 use UNION ALL + DISTINCT instead of
UNION for better performance
@Hritiksingh
AGGREGATING
DATA
17 Use columnstore indexes for queries
involving heavy aggregations on large
tables
Pre-aggregate data and store the
18 results in a separate table for faster
reporting
@Hritiksingh
SUBQUERIES
19 Understand when to use JOIN, EXISTS, or
IN. Avoid IN with large lists as it can be
inefficient
Simplify your queries by eliminating
20 redundant logic and conditions by
using CTE
@Hritiksingh
DDL
21 Avoid VARCHAR or TEXT types
unnecessarily; choose precise data types
to save storage and improve performance
Avoid defining excessive lengths in
22 your data types (e.g.,
VARCHAR(MAX)) unless truly needed
Use NOT NULL constraints wherever
23 possible to enforce data integrity
Ensure all tables have a clustered
24 primary key to provide structure and
improve query performance
Add non-clustered indexes to foreign
25 keys that are frequently queried to
speed up lookups
@Hritiksingh
INDEXING
26 Avoid Over Indexing, as it can slow down
insert, update, and delete operations
Regularly review and drop unused
27 indexes to save space and improve
write performance
Update table statistics weekly to ensure
28 the query optimizer has the most up-to-
date information
Reorganize and rebuild fragmented
29 indexes weekly to maintain query
performance.
For large tables (e.g., fact tables),
30 partition the data and then apply a
columnstore index for best
performance results
@Hritiksingh
