Best Practices for Writing SQL Queries

As our data footprints grow, writing efficient SQL is more important than ever. Here are some proven SQL optimization strategies I’ve found invaluable: ☞ Choose Selective Indexes Wisely: Index columns used frequently in WHERE, JOIN, or ORDER BY clauses. But remember—too many indexes can slow down writes. ☞ Avoid SELECT *: Instead of fetching all columns, specify only what you need. This cuts down I/O and improves speed. ☞ Use Joins Efficiently: Filter tables before joining them, and prefer INNER JOIN when possible for better performance. ☞ Analyze Query Execution Plans: SQL tools like EXPLAIN help pinpoint where queries can be improved—don’t skip this step! ☞ Bookmark Common Subqueries with CTEs or Temp Tables: Reusing computations avoids redundant processing. ☞ Limit Returned Rows: Use LIMIT to fetch only what is necessary, especially for reporting or API use. ☞ Normalize (but not over-normalize): Keep your schema neat, but sometimes denormalizing for reads makes sense. #SQL #DataEngineering #DatabasePerformance #TechTips

SQL Query Optimization Best Practices Optimizing SQL queries in SQL Server is crucial for improving performance and ensuring efficient use of database resources. Here are some best practices for SQL query optimization in SQL Server: 1). Use Indexes Wisely: a. Identify frequently used columns in WHERE, JOIN, and ORDER BY clauses and create appropriate indexes on those columns. b. Avoid over-indexing as it can degrade insert and update performance. c. Regularly monitor index usage and performance to ensure they are providing benefits. 2). Write Efficient Queries: a. Minimize the use of wildcard characters, especially at the beginning of LIKE patterns, as it prevents the use of indexes. b. Use EXISTS or IN instead of DISTINCT or GROUP BY when possible. c. Avoid using SELECT * and fetch only the necessary columns. d. Use UNION ALL instead of UNION if you don't need to remove duplicate rows, as it is faster. e. Use JOINs instead of subqueries for better performance. f. Avoid using scalar functions in WHERE clauses as they can prevent index usage. 3). Optimize Joins: a. Use INNER JOIN instead of OUTER JOIN if possible, as INNER JOIN typically performs better. b. Ensure that join columns are indexed for better join performance. c. Consider using table hints like (NOLOCK) if consistent reads are not required, but use them cautiously as they can lead to dirty reads. 4). Avoid Cursors and Loops: a. Use set-based operations instead of cursors or loops whenever possible. b. Cursors can be inefficient and lead to poor performance, especially with large datasets. 5). Use Query Execution Plan: a. Analyze query execution plans using tools like SQL Server Management Studio (SSMS) or SQL Server Profiler to identify bottlenecks and optimize queries accordingly. b. Look for missing indexes, expensive operators, and table scans in execution plans. 6). Update Statistics Regularly: a. Keep statistics up-to-date by regularly updating them using the UPDATE STATISTICS command or enabling the auto-update statistics feature. b. Updated statistics help the query optimizer make better decisions about query execution plans. 7. Avoid Nested Queries: a. Nested queries can be harder for the optimizer to optimize effectively. b. Consider rewriting them as JOINs or using CTEs (Common Table Expressions) if possible. 8. Partitioning: a. Consider partitioning large tables to improve query performance, especially for queries that access a subset of data based on specific criteria. 9. Use Stored Procedures: a. Encapsulate frequently executed queries in stored procedures to promote code reusability and optimize query execution plans. 10). Regular Monitoring and Tuning: a. Continuously monitor database performance using SQL Server tools or third-party monitoring solutions. b. Regularly review and tune queries based on performance metrics and user feedback.  #sqlserver #performancetuning #database #mssql

With a background in data engineering and business analysis, I’ve consistently seen the immense impact of optimized SQL code on improving the performance and efficiency of database operations. It indirectly contributes to cost savings by reducing resource consumption. Here are some techniques that have proven invaluable in my experience: 1. Index Large Tables: Indexing tables with large datasets (>1,000,000 rows) greatly speeds up searches and enhances query performance. However, be cautious of over-indexing, as excessive indexes can degrade write operations. 2. Select Specific Fields: Choosing specific fields instead of using SELECT * reduces the amount of data transferred and processed, which improves speed and efficiency. 3. Replace Subqueries with Joins: Using joins instead of subqueries in the WHERE clause can improve performance. 4. Use UNION ALL Instead of UNION: UNION ALL is preferable over UNION because it does not involve the overhead of sorting and removing duplicates. 5. Optimize with WHERE Instead of HAVING: Filtering data with WHERE clauses before aggregation operations reduces the workload and speeds up query processing. 6. Utilize INNER JOIN Instead of WHERE for Joins: INNER JOINs help the query optimizer make better execution decisions than complex WHERE conditions. 7. Minimize Use of OR in Joins: Avoiding the OR operator in joins enhances performance by simplifying the conditions and potentially reducing the dataset earlier in the execution process. 8. Use Views: Creating views instead of results that can be accessed faster than recalculating the views each time they are needed. 9. Minimize the Number of Subqueries: Reducing the number of subqueries in your SQL statements can significantly enhance performance by decreasing the complexity of the query execution plan and reducing overhead. 10. Implement Partitioning: Partitioning large tables can improve query performance and manageability by logically dividing them into discrete segments. This allows SQL queries to process only the relevant portions of data. #SQL #DataOptimization #DatabaseManagement #PerformanceTuning #DataEngineering

Understanding SQL query execution order is fundamental to writing efficient and correct queries. Let me break down this crucial concept that many developers overlook. 𝗛𝗼𝘄 𝗪𝗲 𝗪𝗿𝗶𝘁𝗲 𝗦𝗤𝗟: 1. SELECT - Choose columns 2. FROM - Specify table 3. WHERE - Filter rows 4. GROUP BY - Group data 5. HAVING - Filter groups 6. ORDER BY - Sort results 7. LIMIT - Restrict rows 𝗕𝘂𝘁 𝗛𝗲𝗿𝗲'𝘀 𝗛𝗼𝘄 𝗦𝗤𝗟 𝗔𝗰𝘁𝘂𝗮𝗹𝗹𝘆 𝗘𝘅𝗲𝗰𝘂𝘁𝗲𝘀: 1. FROM - First identifies the tables 2. WHERE - Filters individual rows 3. GROUP BY - Creates groups 4. HAVING - Filters groups 5. SELECT - Finally processes column selection 6. ORDER BY - Sorts the results 7. LIMIT - Caps the result set 𝗪𝗵𝘆 𝗧𝗵𝗶𝘀 𝗠𝗮𝘁𝘁𝗲𝗿𝘀: • Understanding this order helps debug query issues • Improves query optimization • Explains why some column aliases work in ORDER BY but not in WHERE • Critical for writing efficient subqueries • Essential for complex query planning 𝗣𝗿𝗼 𝗧𝗶𝗽𝘀: 1. Can't use column aliases in WHERE because SELECT executes after WHERE 2. HAVING requires GROUP BY (mostly) as it executes right after 3. Window functions process after SELECT phase 4. ORDER BY can use aliases as it executes after SELECT 𝗥𝗲𝗮𝗹-𝗪𝗼𝗿𝗹𝗱 𝗜𝗺𝗽𝗮𝗰𝘁: Understanding this execution order is crucial for: - Query Performance Optimization - Debugging Complex Queries - Writing Maintainable Code - Database Design Decisions - Handling Large Datasets ⚠️ Common Pitfalls: ```𝚜𝚚𝚕 𝚂𝙴𝙻𝙴𝙲𝚃 𝚎𝚖𝚙𝚕𝚘𝚢𝚎𝚎_𝚗𝚊𝚖𝚎, 𝙰𝚅𝙶(𝚜𝚊𝚕𝚊𝚛𝚢) 𝚊𝚜 𝚊𝚟𝚐_𝚜𝚊𝚕𝚊𝚛𝚢 𝙵𝚁𝙾𝙼 𝚎𝚖𝚙𝚕𝚘𝚢𝚎𝚎𝚜 𝚆𝙷𝙴𝚁𝙴 𝚊𝚟𝚐_𝚜𝚊𝚕𝚊𝚛𝚢 > 𝟻𝟶𝟶𝟶𝟶  -- 𝚃𝚑𝚒𝚜 𝚠𝚘𝚗'𝚝 𝚠𝚘𝚛𝚔! 𝙶𝚁𝙾𝚄𝙿 𝙱𝚈 𝚎𝚖𝚙𝚕𝚘𝚢𝚎𝚎_𝚗𝚊𝚖𝚎 ``` ✅ Correct Approach: ```𝚜𝚚𝚕 𝚂𝙴𝙻𝙴𝙲𝚃 𝚎𝚖𝚙𝚕𝚘𝚢𝚎𝚎_𝚗𝚊𝚖𝚎, 𝙰𝚅𝙶(𝚜𝚊𝚕𝚊𝚛𝚢) 𝚊𝚜 𝚊𝚟𝚐_𝚜𝚊𝚕𝚊𝚛𝚢 𝙵𝚁𝙾𝙼 𝚎𝚖𝚙𝚕𝚘𝚢𝚎𝚎𝚜 𝙶𝚁𝙾𝚄𝙿 𝙱𝚈 𝚎𝚖𝚙𝚕𝚘𝚢𝚎𝚎_𝚗𝚊𝚖𝚎 𝙷𝙰𝚅𝙸𝙽𝙶 𝙰𝚅𝙶(𝚜𝚊𝚕𝚊𝚛𝚢) > 𝟻𝟶𝟶𝟶𝟶  -- 𝚃𝚑𝚒𝚜 𝚠𝚘𝚛𝚔𝚜! ``` Next Steps: • Review your existing queries • Identify optimization opportunities • Refactor problematic queries • Share this knowledge with your team

17 lessons I learned about Query Optimization over the last 8 years and 9 months at Amazon...(It took me a lot of slow queries to realize these, but you don't have to!) 1. Never assume you know what's slow → always read the execution plan before changing anything. 2. Optimize in small, targeted steps instead of rewriting the entire query to avoid breaking correctness. 3. Readable queries > Clever queries → if your 500-line CTE masterpiece confuses everyone, it's unmaintainable. 4. Understand the data distribution first → a query that works on 1M rows might explode on 1B rows with skewed data. 5. Query optimization is a habit → review slow queries continuously, not just when the CEO complains the dashboard is frozen. 6. Simplify, don't complicate → sometimes removing a subquery or unnecessary join is all you need. 7. Focus on data layout, not just SQL tricks → proper partitioning and clustering beats query rewrites every time. 8. Reduce data scanned → scanning less data is always faster and cheaper than scanning everything with a better algorithm. 9. Performance matters more than you think → a query taking 10 minutes vs 10 seconds is the difference between interactive analytics and batch hell. 10. Legacy queries aren't scary → but optimizing them without understanding the business logic is a nightmare. 11. Don't change too much at once → rewriting joins, adding indexes, and changing partitions simultaneously makes debugging impossible. 12. Know your goal before optimizing → lower latency? Reduce cost? Handle more concurrency? Define it first. 13. Favor filters early over filters late → push predicates down to scan less data, don't scan everything then filter. 14. Indexes aren't always your friend → they speed up reads but slow down writes, and they cost storage. Use them strategically. 15. Optimize what runs most often → a query running 10,000 times/day with 5-second latency wastes more resources than a 1-hour monthly report. 16. Queries are for humans too → write them so your future self (and your team) can understand the logic without a PhD. 17. Slow queries are a liability → ignoring them today means angry users, blown SLAs, and expensive compute bills tomorrow.

Maybe you can WRITE SQL, but are you writing ✨GOOD SQL✨? SQL is more than just writing a query without errors… Here’s 10 query optimization tips: 1. Avoid SELECT * and instead list desired columns 2. Use INNER JOINs over LEFT JOINs when applicable 3. Use WHERE and LIMIT to filter rows 4. Filter as much as possible as early as possible (consider the order of execution) 5. Avoid ORDER BY (especially in subqueries and CTEs) 6. Avoid using DISTINCT unless necessary (especially when it’s already implied like in GROUP BY & UNION) 7. Use CTEs when you’ll have to refer to a table/ouput multiple times 8. Avoid using wildcards at the beginning of a string (‘%jess%’ vs. ‘jess%’) 9. Use EXISTS instead of COUNT and IN 10. Avoid complex logic Obviously you can’t ALWAYS avoid these, and they each have their use cases, but these are good things to think about when optimizing your queries.

After optimizing 100+ SQL queries, these 7 techniques delivered 90% of the performance gains: Bad SQL is not just slow; it costs memory, kills performance, and frustrates teams. Here are 7 practical, high-impact optimization habits you can start applying today. 1. 𝐀𝐯𝐨𝐢𝐝 𝐒𝐄𝐋𝐄𝐂𝐓 * - Pulling unnecessary columns wastes memory and slows scans. - Select only what you need for faster, leaner queries. 2. 𝐈𝐧𝐝𝐞𝐱 𝐘𝐨𝐮𝐫 𝐉𝐎𝐈𝐍 + 𝐖𝐇𝐄𝐑𝐄 𝐂𝐨𝐥𝐮𝐦𝐧𝐬 - Without indexes, SQL scans entire tables. - Add indexes on frequently filtered + joined columns to speed up lookups instantly. 3. 𝐔𝐬𝐞 𝐄𝐗𝐈𝐒𝐓𝐒 𝐈𝐧𝐬𝐭𝐞𝐚𝐝 𝐨𝐟 𝐈𝐍 - IN loads the full subquery. - EXISTS stops at the first match. - Less work, faster output. 4. 𝐔𝐬𝐞 𝐋𝐈𝐌𝐈𝐓 𝐖𝐡𝐢𝐥𝐞 𝐓𝐞𝐬𝐭𝐢𝐧𝐠 - Don't scan millions of rows just to check query logic. - LIMIT cuts workload and protects your database during development. 5. 𝐑𝐞𝐩𝐥𝐚𝐜𝐞 𝐂𝐨𝐫𝐫𝐞𝐥𝐚𝐭𝐞𝐝 𝐒𝐮𝐛𝐪𝐮𝐞𝐫𝐢𝐞𝐬 𝐰𝐢𝐭𝐡 𝐉𝐎𝐈𝐍𝐬 - Correlated subqueries run once per row. - JOINs run once total. - A 10-minute query can drop to seconds. 6. 𝐏𝐚𝐫𝐭𝐢𝐭𝐢𝐨𝐧 𝐋𝐚𝐫𝐠𝐞 𝐓𝐚𝐛𝐥𝐞𝐬 𝐛𝐲 𝐃𝐚𝐭𝐞 - Why scan years of data when you need one month? - Date partitions help SQL target only relevant segments. 7. 𝐀𝐥𝐰𝐚𝐲𝐬 𝐂𝐡𝐞𝐜𝐤 𝐄𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧 𝐏𝐥𝐚𝐧𝐬 - Use EXPLAIN / EXPLAIN ANALYZE to see what SQL is actually doing. - A single index often beats every fancy trick. 𝐒𝐐𝐋 𝐨𝐩𝐭𝐢𝐦𝐢𝐳𝐚𝐭𝐢𝐨𝐧 𝐢𝐬 𝐬𝐢𝐦𝐩𝐥𝐞: Measure → Fix → Measure again. Small tweaks = massive speed gains. If you want to master query optimization systematically, the "𝐈𝐦𝐩𝐫𝐨𝐯𝐢𝐧𝐠 𝐐𝐮𝐞𝐫𝐲 𝐏𝐞𝐫𝐟𝐨𝐫𝐦𝐚𝐧𝐜𝐞 𝐢𝐧 𝐏𝐨𝐬𝐭𝐠𝐫𝐞𝐒𝐐𝐋" course covers advanced techniques with hands-on exercises. 𝐂𝐡𝐞𝐜𝐤 𝐢𝐭 𝐨𝐮𝐭 𝐡𝐞𝐫𝐞 → https://lnkd.in/dxzWPN9Z Get 𝟏𝟓𝟎+ real interview questions (with solutions & frameworks) in our Data Analyst Interview Prep Book: https://lnkd.in/dyzXwfVp 𝐏.𝐒. Join 20,000+ readers getting weekly data insights in my newsletter → https://lnkd.in/dUfe4Ac6

Here's my Ultimate SQL Query Optimization Cheatsheet: (Save this - slow queries will cost you in production and in interviews) Writing a query that works is the baseline. Writing a query that works fast is the skill. I have seen analysts submit queries that took 45 seconds to load on a dashboard used by 200 people every morning. That is not a data problem. That is an optimization problem. Here are 8 techniques that fix it 👇 1. Use Indexes Effectively Indexes turn slow full table scans into fast direct lookups. 2. Avoid SELECT Selecting only required columns reduces memory usage and improves query performance. 3. Use EXISTS Instead of IN EXISTS stops early on match, improving performance for large datasets. 4. Optimize JOINs with Indexed Columns Indexed join columns prevent repeated scans and significantly speed up joins. 5. Filter Early with WHERE Before GROUP BY Filtering early reduces rows processed, making aggregations faster and efficient. 6. Avoid Functions on Indexed Columns Functions on indexed columns disable indexes and force full table scans. 7. Use LIMIT to Reduce Data Load Limit results to necessary rows to avoid unnecessary data processing overhead. 8. Use Proper Data Types Matching data types ensures indexes work correctly and avoids hidden performance issues. Here is the honest truth: Most analysts never think about query performance until something breaks in production. By then it is too late. The analysts who get promoted are the ones who write queries that work for a team of 5 today and still work when 500 people are hitting that dashboard every morning. Optimization is not advanced SQL. It is professional SQL. Which of these mistakes are you still making? ♻️ Repost to help someone level up their SQL 💭 Tag a data analyst who needs to see this 📩 Get my full SQL career guide: https://lnkd.in/gjUqmQ5H

𝗗𝗮𝘆 𝟰/𝟯𝟬 — 𝗦𝗤𝗟 𝗤𝘂𝗲𝗿𝘆 𝗢𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻: 𝗧𝗵𝗲 𝗦𝗸𝗶𝗹𝗹 𝗧𝗵𝗮𝘁 𝗧𝘂𝗿𝗻𝘀 𝗚𝗼𝗼𝗱 𝗗𝗘𝘀 𝗶𝗻𝘁𝗼 𝗚𝗿𝗲𝗮𝘁 𝗗𝗘𝘀 👉 What People Think… “If the query works, that’s enough.” “Performance issues = server problem.” “Indexes magically speed everything up.” “SELECT * is harmless.” 👉 What Actually Happens… SQL performance depends on how efficiently your query interacts with data: filters → joins → indexes → scans → execution plans. The difference between a good query and a great query is often 10x speed. 🔟 𝗦𝗤𝗟 𝗢𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗮𝗰𝘁𝗶𝗰𝗲𝘀 𝗘𝘃𝗲𝗿𝘆 𝗗𝗮𝘁𝗮 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿 𝗠𝘂𝘀𝘁 𝗞𝗻𝗼𝘄 1️⃣ Use Indexes Smartly Indexes shine on high-cardinality columns. Don’t index everything — it slows writes. 2️⃣ Avoid SELECT * Selecting only required columns reduces scan time + network I/O. 3️⃣ Use EXISTS instead of IN (for large subqueries) EXISTS stops after the first match → usually faster. 4️⃣ Optimize JOINs using indexed keys Joining on unindexed columns is one of the biggest performance killers. 5️⃣ Filter Early (WHERE before GROUP BY/HAVING) Shrinking the dataset early = faster computation later. 6️⃣ Avoid functions on indexed columns WHERE DATE(timestamp) blocks index usage completely. 7️⃣ Prefer UNION ALL instead of UNION UNION = deduplication = expensive sorting. 8️⃣ Don’t do wildcard-leading searches LIKE '%text' → full scan. LIKE 'text%' → index friendly. 9️⃣ Partition large tables Especially for time-series data; improves pruning dramatically. 🔟 Always check the query plan (EXPLAIN) Most engineers don’t — but this shows WHERE the bottleneck really is. #dataengineering #sqlinterview #techinterviewprep #30daysofde