SQL Query Optimization: How I Reduced a 4-Hour Query Execution Time to Just 8 Minutes

Writing a SQL query that runs is just not good enough in practice. Production environments, where millions of records have to be processed for the same query, is a different ball game all together. I had an SQL query running in production very recently that was running for nearly 4 hours. This query was commonplace in a reporting process which retrieved data from multiple transactional tables with complex joins, aggregations, and conditional logic. It was not all just execution time there was direct impact on report generation and operational workflows.

step one-->I began investigating the real bottlenecks step by step rather than rewriting everything completely. Track the Real Issue is

Initially, the query looked normal. It had:

Multiple joins Nested subqueries Aggregations Conditional filters Large transactional tables However, when I checked the behavior of executions, I found some significant issues:

Very large tables were scanned in a full table scans Repetitive calculations are repeated multiple times Unnecessary row expansion by some joins Filtering was taking the wrong signal in their execution flow Intermediate result sets were ballooning It is here that we bring practical SQL optimization.

Step 2 --> Cut Down On Processing Unessential Data The first one was to filter sooner the data.

Was joining huge datasets first and filtering afterwards. I modified the flow to perform filtering before joins.

For example --> instead of: SELECT * FROM ProductionData P JOIN EventLogs E ON P.BatchId = E.BatchId WHERE E.DateAndTime >= '2026-01-01 00:00'

Processing the EventLogs dataset reduction --> WITH FilteredLogs AS ( SELECT FROM EventLogs WHERE DateAndTime >= '2026-01-01 00:00' ) SELECT FROM ProductionData P JOIN FilteredLogs E ON P.BatchId = E.BatchId Comparatively this removed a lot of unnecessary scanning by itself.

Step 3 -->Replacing Repeated Subqueries

One such common problem was correlated subqueries being repeated.

For every row, the same calculations were running multiple times. I moved the logic from being recalculated over and over again in multiple CTEs into temporary datasets, reusable CTE.

This reduced CPU usage significantly.

Step 4 --> Index Optimization

This was one of the most significant enhancements. When I checked the execution plan, to my astonishment I found that some of the important columns used in joins and filtering had no indexes on them at all. I added indexes on:

frequently filtered columns join columns datetime-based search columns Example:

CREATE NONCLUSTERED INDEX IX_EventLogs_BatchId_Date ON EventLogs(BatchId, DateAndTime) Reduced Table Scans After indexing

Step 5-->Following are the steps involved Removing Unnecessary DISTINCT and Functions Thus, in certain locations functions were being used strictly in the WHERE conditions.

Example: This SQL query filters rows from a table based on the date format regardless whether the CreatedDate column is of type datetype or other.

This prevented index usage. Instead, I changed it to:

WHERE CreatedDate >= '2026-04-25' AND CreatedDate < '2026-04-26' This meant we could actually start using indexes in SQL Server the way they were intended.

Step 6--> Execution Plan Analysis Reading the execution plan was one of the key steps.

This part is ignored by a vast majority of developers but execution plans show where SQL Server spends maximum time. I identified:

expensive scans hash matches unnecessary sorts missing index recommendations A huge difference was made by basing optimizations off of real execution behavior.

Final Result After multiple optimization improvements -->Aggregated query execution time, from 4 h to 8 min CPU usage reduced significantly Report generation became stable Reduced blocking and timeout issues Overall database performance improved Key Learning This experience taught me one thing:

It is not only about writing the queries while SQL optimization. It is about understanding:

data flow execution behavior indexing strategy filtering logic memory usage and the implementation of SQL Server on how it actually processes data A small change in logic helps save a few hours of execution time at times.

Final Thoughts -->True optimization is from real debugging, testing and knowledge of how DBs perform under actual prod load.

Performance tuning is one of the most important skills for all who work with SQL in real world enterprise systems.

Always analyze execution plans, eliminate unnecessary computation, and streamline the flow of data when dealing with big datasets optimize before scaling up the infrastructure itself.

#T-SQL #DeveloperData #EngineeringData #AnalyticsBusiness #IntelligenceQuery #optimizationPerformance #tuningSQL #ServerSQLServer #GrowthMindSet

SQL