3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL?
| by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Open in app
Search
Member-only story
How to Optimize Long-Running Queries in
SQL?
Prem Vishnoi(cloudvala) · Following
Published in EndToEndData
4 min read · Feb 22, 2025
Listen Share More
[Link] 1/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
[Link] 2/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
[Link] 3/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
[Link] 4/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Optimizing long-running SQL queries is a critical task for improving database
performance.
Here are some practical strategies to help we identify bottlenecks and speed up our
queries:
1. Analyze and Understand the Query
Use EXPLAIN or EXPLAIN ANALYZE: Most SQL databases (e.g., PostgreSQL,
MySQL, SQL Server) offer an EXPLAIN command to show the query execution
plan.
This reveals how the database processes our query, including which indexes are
used, the order of operations, and estimated costs.
[Link] 5/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Identify Slow Parts: Look for full table scans, expensive joins, or sorting
operations that could be optimized.
2. Indexing
[Link] 6/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Add Indexes: Create indexes on columns used in WHERE, JOIN, GROUP BY, or
ORDER BY clauses. For example:
[Link] 7/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
CREATE INDEX idx_column_name ON table_name(column_name);
Use Composite Indexes: If our query filters or joins on multiple columns, a
composite index might help:
CREATE INDEX idx_composite ON table_name(column1, column2);
Avoid Over-Indexing: Too many indexes can slow down INSERT, UPDATE, and
DELETE operations, so balance read vs. write performance.
3. Rewrite the Query
[Link] 8/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
[Link] 9/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
[Link] 10/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Simplify Complex Queries: Break down large queries into smaller, more
manageable parts (e.g., using temporary tables or CTEs).
Avoid SELECT *** : Specify only the columns our need:
SELECT column1, column2 FROM table_name;
Optimize Joins: Use INNER JOIN instead of LEFT JOIN where possible, and
ensure join conditions use indexed columns.
Filter Early: Apply WHERE conditions to reduce the dataset before joining or
grouping
SELECT [Link], [Link]
FROM table_a a
JOIN table_b b ON [Link] = [Link]
WHERE [Link] > '2024-01-01';
4. Limit Data Scanned
Use LIMIT: If we only need a subset of results, add a LIMIT clause
[Link] 11/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
SELECT * FROM table_name WHERE condition LIMIT 100;
Partition Large Tables: For very large tables, consider partitioning by date,
range, or another logical division to reduce the data scanned.
Avoid Unnecessary Sorting: Remove or optimize ORDER BY if it’s not critical, as
sorting can be resource-intensive.
5. Optimize Subqueries
Replace Subqueries with Joins: Subqueries can sometimes be less efficient than
joins:
-- Instead of:
SELECT * FROM table_a WHERE id IN (SELECT id FROM table_b);
-- Use:
SELECT a.* FROM table_a a JOIN table_b b ON [Link] = [Link];
Use EXISTS: For checking existence, EXISTS can be faster than IN.
6. Caching
[Link] 12/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Materialized Views: If the data doesn’t change often, store the query results in a
materialized view and refresh it periodically.
Query Caching: Enable database-level caching (e.g., MySQL Query Cache, if
available) or application-level caching (e.g., Redis) for frequently run queries.
7. Database Configuration
[Link] 13/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Tune Parameters: Adjust memory settings (e.g., work_mem in PostgreSQL,
buffer pool in MySQL) to give the database more resources for query execution.
Update Statistics: Ensure the database’s statistics are current (e.g., ANALYZE in
PostgreSQL) so the query planner makes informed decisions.
8. Profiling and Testing
[Link] 14/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Profile the Query: Use tools like SQL Server Profiler, PostgreSQL’s
pg_stat_statements, or MySQL’s Performance Schema to measure execution time
and resource usage.
Test Changes: Benchmark our query before and after optimizations to confirm
improvements.
Example
Suppose we have this slow query:
[Link] 15/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = [Link]
WHERE o.order_date >= '2023-01-01'
ORDER BY o.order_date;
Optimization Steps:
1. Add an index: CREATE INDEX idx_orders_date ON orders(order_date);
2. Specify columns: SELECT o.order_date, [Link] instead of SELECT *.
3. Ensure customer_id and id are indexed (usually primary keys are by default).
Final Thoughts
The best approach depends on our specific query, data size, and database system.
Start by analyzing the execution plan, then apply targeted optimizations like
indexing or rewriting.
Sql Database Optimization Programming Data Engineering Tech
Follow
Published in EndToEndData
12 Followers · Last published 1 day ago
EndToEndData is the ultimate destination for data engineers and architects looking to ace interviews,
uncover innovative ideas, and master data architecture theory. We cover the full spectrum — from core
principles to complete solutions — offering practical advice, fresh insights
Following
Written by Prem Vishnoi(cloudvala)
[Link] 16/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
963 Followers · 86 Following
Head of Data and ML experienced in designing, implementing, and managing large-scale data
infrastructure. Skilled in ETL, data modeling, and cloud computing
Responses (1)
Dime
What are your thoughts?
TechieTreeHugger
Feb 23
Some really nice suggestions on optimization techniques, I will definitely try some of them.
9 1 reply Reply
More from Prem Vishnoi(cloudvala) and EndToEndData
[Link] 17/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
In Towards Dev by Prem Vishnoi(cloudvala)
Apache Spark Architecture :A Deep Dive into Big Data Processing
Agenda
Feb 6 76
In EndToEndData by Prem Vishnoi(cloudvala)
How Spark Handles Large-Scale Data Processing
Spark is built for big data, processing massive datasets across a cluster with speed and
resilience. Here’s how it works, with an example…
[Link] 18/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Feb 23 57
In EndToEndData by Prem Vishnoi(cloudvala)
Interview prepare for Mpower Group for Data Engineer
Core Programming & Querying
Feb 13 51
In NextGenAI by Prem Vishnoi(cloudvala)
Building a Machine Learning Pipeline to Predict Customer Churn
[Link] 19/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Overview :
Jan 25 20
See all from Prem Vishnoi(cloudvala)
See all from EndToEndData
Recommended from Medium
Vijay Gadhave
Delta Lake 4.0: Next-Level Big Data Management
Note: If you’re not a medium member, CLICK HERE
Feb 21 25 1
[Link] 20/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
In Data Engineer Things by B V Sarath Chandra
Dell Pyspark Interview Question
Problem Statement
Feb 26 7 1
Lists
General Coding Knowledge
20 stories · 1933 saves
Stories to Help You Grow as a Software Developer
19 stories · 1615 saves
Coding & Development
11 stories · 1022 saves
Apple's Vision Pro
7 stories · 85 saves
[Link] 21/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Think Data
My Interview Experience for Senior Data Engineer at Walmart
I’m here to share my Senior Data Engineer interview experience with Walmart from December
2024. If you’re preparing for this role, I hope…
Feb 22 137 5
In DataHub by Maggie Hays
What’s Next for DataHub?
A Sneak Peek into the 2025 Roadmap DataHub
[Link] 22/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Feb 24 22
Yousef Yousefi
Optimizing Apache Spark for Large-Scale Data Processing
Apache Spark has evolved as the cornerstone for distributed data processing at scale,
enabling rapid data transformation and analysis…
Feb 19 2
In Level Up Coding by Santosh Shinde
Medallion Architecture: Principles and Practical Exploration
[Link] 23/24
3/5/25, 10:06 AM How to Optimize Long-Running Queries in SQL? | by Prem Vishnoi(cloudvala) | EndToEndData | Feb, 2025 | Medium
Data Layout Approach: A Modern Approach to Scalable Data Lakehouse Design and
Understanding with Databricks notebook
Feb 15 108
See more recommendations
[Link] 24/24
