DataNotesSQL
30 SQL Tips and Tricks From 15 Years of Experience
In this issue3 min left
Hey friends!👋
Over the past 15 years of working on SQL projects, I’ve learned a ton about optimizing performance, and today I’m excited to share 30 of my best tips and tricks with you.
To make it even easier, I’ve put together a handy Cheat Sheet for quick reference! And if you want all the details, I’ve just published a video where I break everything down step by step. Check it out!
30 SQL performance tips: the cheat sheetPDF, 2.7 MB
FETCHING DATA
#1 Tip | Only select the columns you need. Avoid using SELECT *
#2 Tip | Avoid using DISTINCT or ORDER BY unless absolutely necessary, as they can slow down queries.
#3 Tip | For exploration, limit the number of rows to avoid fetching unnecessary data.
FILTERING DATA
#4 Tip | Create non-clustered indexes on columns frequently used in the WHERE clause to speed up queries.
#5 Tip | Avoid applying functions (e.g., UPPER(), DATE()) to columns in the WHERE clause, as this prevents indexes from being used.
#6 Tip | Avoid starting string searches with a wildcard (%example), as this disables index usage.
#7 Tip | Use IN instead of multiple OR conditions for better readability and performance.
JOINING DATA
#8 Tip | Understand the performance implications of different join types. Use INNER JOIN when possible for efficiency.
#9 Tip | Always use explicit (ANSI-style) joins (INNER JOIN, LEFT JOIN, etc.) instead of older implicit join syntax.
#10 Tip | Ensure that the columns in the ON clause of your joins are indexed for optimal performance.
#11 Tip | Filter before joining large tables to reduce the size of the dataset being joined.
#12 Tip | Aggregate before joining large tables to reduce the size of the dataset being joined.
#13 Tip | Replace OR conditions in join logic with UNION where possible to improve query performance.
#14 Tip | Be aware of nested loops in your query execution plan. Use SQL Hints if needed to optimize performance.
#15 Tip | Use UNION ALL instead of UNION if duplicates are acceptable, as it is faster.
#16 Tip | When duplicates are not acceptable, use UNION ALL + DISTINCT instead of UNION for better performance.
AGGREGATING DATA
#17 Tip | Use columnstore indexes for queries involving heavy aggregations on large tables.
#18 Tip | Pre-aggregate data and store the results in a separate table for faster reporting.
SUBQUERIES
#19 Tip | Understand when to use JOIN, EXISTS, or IN. Avoid IN with large lists as it can be inefficient.
#20 Tip | Simplify your queries by eliminating redundant logic and conditions by using CTE
DDL (Data Definition Language)
#21 Tip | Avoid VARCHAR or TEXT types unnecessarily; choose more precise data types to save storage and improve performance.
#22 Tip | Avoid defining excessive lengths in your data types (e.g., VARCHAR(MAX)) unless truly needed.
#23 Tip | Use NOT NULL constraints wherever possible to enforce data integrity.
#24 Tip | Ensure all tables have a clustered primary key to provide structure and improve query performance.
#25 Tip | Add non-clustered indexes to foreign keys that are frequently queried to speed up lookups.
INDEXING
#26 Tip | Avoid Over Indexing, as it can slow down insert, update, and delete operations.
#27 Tip | Regularly review and drop unused indexes to save space and improve write performance.
#28 Tip | Update table statistics weekly to ensure the query optimizer has the most up-to-date information.
#29 Tip | Reorganize and rebuild fragmented indexes weekly to maintain query performance.
#30 Tip | For large tables (e.g., fact tables), partition the data and then apply a columnstore index for best performance results.
I hope you found this helpful, and if you have any questions or want to share your own tips, I’m all ears! Happy querying!
Baraa Khatib Salkini