If you've ever run a query that took 3 minutes to return 5,000 rows — this guide is for you. SQL query optimization is one of the highest-value skills a Data Analyst can have, yet most tutorials gloss over it. I'll share 10 techniques I use every day on real production databases.
Examples use SQL Server (T-SQL) but most principles apply to MySQL, PostgreSQL, and other relational databases. I'll note exceptions where syntax differs.
1. Use the Execution Plan — Always
Before optimizing anything, read the execution plan. In SSMS: Query → Include Actual Execution Plan (Ctrl+M), then run. Look for these red flags:
- Table Scan — reading every row. Needs an index.
- Key Lookup — fetching extra columns after an index seek. Fix by including columns in the index.
- High estimated vs actual rows — stale statistics. Run
UPDATE STATISTICS tablename. - Nested Loop with many iterations — usually an N+1 problem.
2. Never Use SELECT *
This single habit causes more slow queries than almost anything else. Fetching all columns forces SQL Server to read more data pages, move more bytes across the network, and prevents index-only scans.
-- ❌ BAD — reads all 40 columns, forces table scan
SELECT *
FROM dbo.FactSales
WHERE SaleDate >= '2024-01-01';
-- ✅ GOOD — only reads needed columns, can use index
SELECT
SaleID,
SaleDate,
CustomerID,
TotalAmount
FROM dbo.FactSales
WHERE SaleDate >= '2024-01-01';3. Write SARGable WHERE Clauses
A SARGable (Search ARGument ABLE) predicate is one that allows the query engine to use an index seek rather than a full scan. The rule: never wrap the column in a function in a WHERE clause.
-- ❌ NOT SARGable — function on column forces full scan
WHERE YEAR(SaleDate) = 2024
WHERE UPPER(CustomerName) = 'ANKIT'
WHERE LEFT(ProductCode, 3) = 'PRD'
-- ✅ SARGable — range on column allows index seek
WHERE SaleDate >= '2024-01-01' AND SaleDate < '2025-01-01'
WHERE CustomerName = 'Ankit' -- use collation, not UPPER()
WHERE ProductCode LIKE 'PRD%' -- prefix LIKE is SARGable4. Index Strategically
The right indexes are the single biggest performance lever. For Data Analyst workloads, focus on:
-- Clustered index: defines physical row order (1 per table)
CREATE CLUSTERED INDEX CIX_FactSales_DateKey
ON dbo.FactSales (SaleDate);
-- Non-clustered index: for frequent filter/join columns
CREATE NONCLUSTERED INDEX NIX_FactSales_CustomerID
ON dbo.FactSales (CustomerID)
INCLUDE (TotalAmount, SaleDate); -- INCLUDE avoids key lookup!
-- Covering index: all columns needed are IN the index
CREATE NONCLUSTERED INDEX NIX_Sales_Cover_Report
ON dbo.FactSales (SaleDate, RegionID)
INCLUDE (CustomerID, ProductID, TotalAmount, Quantity);Each index slows down INSERT/UPDATE/DELETE operations. For OLAP-style analytics tables, more indexes are fine. For transactional tables, be selective.
5. Use CTEs to Replace Nested Subqueries
CTEs (Common Table Expressions) don't always improve performance, but they dramatically improve readability — which means fewer bugs and easier maintenance. They're also required for recursive queries.
-- ❌ Hard to read nested subquery
SELECT r.RegionName, s.TotalRevenue
FROM (
SELECT RegionID, SUM(TotalAmount) AS TotalRevenue
FROM (
SELECT * FROM FactSales WHERE SaleDate >= '2024-01-01'
) y
GROUP BY RegionID
) s
JOIN DimRegion r ON r.RegionID = s.RegionID;
-- ✅ Clean CTE version — same performance, much clearer
WITH RecentSales AS (
SELECT RegionID, TotalAmount
FROM FactSales
WHERE SaleDate >= '2024-01-01'
),
RegionRevenue AS (
SELECT RegionID, SUM(TotalAmount) AS TotalRevenue
FROM RecentSales
GROUP BY RegionID
)
SELECT r.RegionName, rv.TotalRevenue
FROM RegionRevenue rv
JOIN DimRegion r ON r.RegionID = rv.RegionID;6. Window Functions Beat Self-Joins
Running totals, rankings, lead/lag comparisons — all of these are traditionally done with slow self-joins. Window functions do the same thing in a single pass.
SELECT
SaleDate,
SalesRepID,
TotalAmount,
-- Running total within each sales rep
SUM(TotalAmount) OVER (
PARTITION BY SalesRepID
ORDER BY SaleDate
ROWS UNBOUNDED PRECEDING
) AS RunningTotal,
-- Rank by revenue within each month
RANK() OVER (
PARTITION BY YEAR(SaleDate), MONTH(SaleDate)
ORDER BY TotalAmount DESC
) AS MonthlyRank,
-- Previous day's sale for the same rep
LAG(TotalAmount, 1, 0) OVER (
PARTITION BY SalesRepID ORDER BY SaleDate
) AS PrevDaySale
FROM dbo.FactSales;7. EXISTS > IN for Large Subqueries
-- ❌ IN: evaluates the entire subquery first
SELECT CustomerID, CustomerName
FROM DimCustomer
WHERE CustomerID IN (
SELECT CustomerID FROM FactSales
WHERE TotalAmount > 10000
);
-- ✅ EXISTS: stops as soon as it finds first match (short-circuits)
SELECT CustomerID, CustomerName
FROM DimCustomer c
WHERE EXISTS (
SELECT 1 -- SELECT 1 is conventional — we just need the match
FROM FactSales s
WHERE s.CustomerID = c.CustomerID
AND s.TotalAmount > 10000
);8. Temp Tables vs CTEs for Large Intermediate Results
When an intermediate result set is large (100K+ rows) and referenced multiple times, materialize it into a temp table. CTEs are re-evaluated each time they're referenced.
-- Materialize large intermediate result once
DROP TABLE IF EXISTS #SalesSummary;
SELECT
RegionID,
ProductID,
SUM(TotalAmount) AS Revenue,
SUM(Quantity) AS Units
INTO #SalesSummary
FROM dbo.FactSales
WHERE SaleDate >= '2024-01-01'
GROUP BY RegionID, ProductID;
-- Create index on temp table for subsequent joins
CREATE INDEX IX_SS_Region ON #SalesSummary (RegionID);
-- Now join multiple times without re-computing
SELECT r.RegionName, s.Revenue
FROM #SalesSummary s
JOIN DimRegion r ON r.RegionID = s.RegionID;
SELECT p.ProductName, s.Units
FROM #SalesSummary s
JOIN DimProduct p ON p.ProductID = s.ProductID;9. Limit Rows Early with TOP / FETCH
-- Quick top N (no offset)
SELECT TOP (10) ProductName, TotalRevenue
FROM ProductSummary
ORDER BY TotalRevenue DESC;
-- Paginated results (page 2, 50 rows per page)
SELECT SaleID, SaleDate, TotalAmount
FROM dbo.FactSales
ORDER BY SaleDate DESC
OFFSET 50 ROWS
FETCH NEXT 50 ROWS ONLY;10. Update Statistics & Rebuild Fragmented Indexes
Even perfect queries go slow on stale statistics or fragmented indexes. Schedule this maintenance weekly on large tables:
-- Check index fragmentation
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i
ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 30 -- >30% = rebuild
ORDER BY ips.avg_fragmentation_in_percent DESC;
-- Rebuild fragmented index
ALTER INDEX NIX_FactSales_CustomerID ON dbo.FactSales REBUILD;
-- Update statistics (run after large data loads)
UPDATE STATISTICS dbo.FactSales WITH FULLSCAN;Read execution plans → Never SELECT * → Write SARGable predicates → Index strategically → Use CTEs for clarity → Window functions over self-joins → EXISTS over IN for large sets → Temp tables for large intermediates → Paginate results → Maintain your indexes weekly.