40. Performance Optimization Case Studies

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Query rewriting for speed
• Index strategy and tuning
• Denormalization trade-offs
• Query plan analysis
• Batch processing optimization
• Caching patterns
• Before/after metrics

Theory -> practice. Real optimization examples. This article teaches measurable improvements.

Case Study 1: Slow Report Query

Problem: Class performance report takes 45 seconds.

-- Original (45 seconds, 2M+ logical reads)
SELECT 
    s.ClassName,
    s.Name,
    COUNT(r.ExamResultId) AS Exams,
    AVG(r.MarksObtained) AS AvgMarks
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
LEFT JOIN Exam e ON r.ExamId = e.ExamId
LEFT JOIN Subject sub ON e.SubjectId = sub.SubjectId
GROUP BY s.StudentId, s.Name, s.ClassName
ORDER BY s.ClassName, AvgMarks DESC;

Analysis:

• Table Scan on ExamResult (all 500K rows)
• Multiple JOINs (expensive)
• Logical reads: 2M (very high)

Optimization: Add indexes

-- Add missing indexes:
CREATE INDEX IX_ExamResult_StudentId ON ExamResult(StudentId);
CREATE INDEX IX_Exam_ExamId ON Exam(ExamId);
CREATE INDEX IX_Subject_SubjectId ON Subject(SubjectId);

-- Re-run same query
-- Result: 5 seconds, 50K logical reads (9x faster)

Alternative: Pre-compute aggregate

-- Create materialized view
CREATE VIEW vw_ClassPerformance WITH SCHEMABINDING AS
SELECT 
    s.ClassName,
    s.Name,
    COUNT(r.ExamResultId) AS Exams,
    AVG(CAST(r.MarksObtained AS DECIMAL(10,2))) AS AvgMarks
FROM dbo.Student s
LEFT JOIN dbo.ExamResult r ON s.StudentId = r.StudentId
GROUP BY s.StudentId, s.Name, s.ClassName;

-- Create clustered index (materializes)
CREATE UNIQUE CLUSTERED INDEX ix_ClassPerf ON vw_ClassPerformance(ClassName, Name);

-- Query now instant (0.1 seconds)
SELECT * FROM vw_ClassPerformance ORDER BY ClassName, AvgMarks DESC;

Result:

• Original: 45 seconds
• With indexes: 5 seconds (9x faster)
• With materialized view: 0.1 seconds (450x faster)

Case Study 2: Fee Collection Report Timeout

Problem: Monthly fee report times out (> 60 seconds).

-- Original (timeout)
SELECT 
    s.ClassName,
    MONTH(p.PaidOn) AS Month,
    SUM(p.Amount) AS Total,
    COUNT(DISTINCT s.StudentId) AS PaidStudents
FROM Student s
INNER JOIN FeeAccount f ON s.StudentId = f.StudentId
INNER JOIN FeePayment p ON f.FeeAccountId = p.FeeAccountId
WHERE YEAR(p.PaidOn) = 2024
GROUP BY s.ClassName, MONTH(p.PaidOn);

Issue: Multiple JOINs, scanning 10M fee payment rows.

Optimization: Denormalize data

-- Add calculated column to FeePayment
ALTER TABLE FeePayment
ADD ClassName NVARCHAR(10);

-- Populate from Student (one-time)
UPDATE fp SET ClassName = s.ClassName
FROM FeePayment fp
INNER JOIN FeeAccount f ON fp.FeeAccountId = f.FeeAccountId
INNER JOIN Student s ON f.StudentId = s.StudentId;

-- Add index
CREATE INDEX IX_FeePayment_ClassMonth ON FeePayment(ClassName, PaidOn);

-- New query (no JOINs, direct table)
SELECT 
    ClassName,
    MONTH(PaidOn) AS Month,
    SUM(Amount) AS Total,
    COUNT(DISTINCT StudentId) AS PaidStudents
FROM FeePayment
WHERE YEAR(PaidOn) = 2024
GROUP BY ClassName, MONTH(PaidOn);

Result:

• Original: Timeout (>60s)
• Optimized: 2 seconds (30x faster)
• Trade-off: Extra column storage, trigger to maintain

Case Study 3: Student Search Performance

Problem: Student search in admin panel sluggish.

-- Original (slow)
SELECT * FROM Student WHERE Name LIKE @SearchTerm + '%';
-- Full table scan, 100K+ students

-- With index on Name
CREATE INDEX IX_Student_Name ON Student(Name);
-- Same query: Still table scan (LIKE wildcard)

-- Optimize: Full-text index
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Student(Name) 
    KEY INDEX PK_Student;

-- Query
SELECT * FROM Student WHERE CONTAINS(Name, @SearchTerm);
-- Result: Instant (subsecond)

Result:

• Original: 3 seconds
• With standard index: 2.5 seconds
• With full-text: 0.1 seconds (30x faster)

Case Study 4: Attendance Report Batch Processing

Problem: Generate attendance for 50K students daily = 10 minutes, locks table.

-- Original (all at once)
INSERT INTO Attendance (StudentId, SubjectId, Date, IsPresent)
SELECT s.StudentId, sub.SubjectId, GETDATE(), 0
FROM Student s
CROSS JOIN Subject sub;
-- Locks: 10 minutes

Optimization: Batch process

-- Batch 1000 at a time
DECLARE @Offset INT = 0;
DECLARE @BatchSize INT = 1000;

WHILE EXISTS (SELECT 1 FROM Student WHERE StudentId > @Offset)
BEGIN
    INSERT INTO Attendance (StudentId, SubjectId, Date, IsPresent)
    SELECT s.StudentId, sub.SubjectId, GETDATE(), 0
    FROM Student s
    CROSS JOIN Subject sub
    WHERE s.StudentId > @Offset
    ORDER BY s.StudentId
    OFFSET 0 ROWS FETCH NEXT @BatchSize ROWS ONLY;
    
    SET @Offset = @Offset + @BatchSize;
    
    -- Allow other queries to run
    WAITFOR DELAY '00:00:01';
END;

Result:

• Original: 10 minutes (table locked)
• Batched: 5 minutes (interleaved with other queries)

---

🎯 Q1: How do I optimize slow report?

1. Check execution plan (scans vs seeks)
2. Add missing indexes on JOIN columns
3. Consider materialized view if expensive aggregate
4. Pre-compute if reporting frequent

🎯 Q2: Should I denormalize for speed?

Trade: Storage + maintenance overhead vs speed.

Denormalize if:

• Aggregate queried frequently
• Speed critical
• Trigger maintains data

Don't denormalize: Single-query slowness.

🎯 Q3: How do I optimize LIKE search?

• LIKE '%term%': Table scan (slow)
• LIKE 'term%': Index seek (fast)
• Full-text search: Best (contains, phrase)

Full-text for complex search.

🎯 Q4: When should I batch process?

Large inserts (>100K rows):

• Batch into 1000-row chunks
• Insert -> delay -> repeat
• Reduces lock time
• Allows other queries to run

Prevents blocking.

🎯 Q5: How do I measure improvement?

SET STATISTICS IO ON;
-- Before optimization
SET STATISTICS IO OFF;

-- Add index

SET STATISTICS IO ON;
-- After optimization
-- Compare: Logical Reads, Elapsed Time

Compare metrics before/after.

🎯 Q6: What's better: index or materialized view?

Index: Fast seeks, maintains automatically. Materialized view: Pre-computed, instant.

Index for: SELECT with WHERE. View for: Complex aggregate repeated often.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Performance optimization case studies in SQL Server. Try these prompts:

• "Show before/after: slow report optimized with index"
• "When should I denormalize for performance?"
• "How do I optimize LIKE search (full-text)?"
• "Show batch processing to reduce lock time"
• "Quiz me: 5 questions about optimization techniques"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

41. API Integration & Backend Patterns ->