There was a time at work when I felt incredibly proud.

We had an old transaction report API used heavily for internal reconciliation. That endpoint was slow not normal slow it took more than 40 seconds to load. The reconciliation team repeatedly approached me to fix it, but the stored procedure behind it was huge, complicated, and honestly… something I avoided for a long time.

One day, with a good mood and a bit of courage, I finally decided to dig into it.

Like many legacy reporting stored procedures, it started simple "just fetch some data with filters" but over the years it grew into a massive, multi-purpose SQL monster. And I found multiple deep-rooted problems.

This blog walks through:

• ✔ The actual issues inside the legacy SP
• ✔ The principles to fix them
• ✔ EF Core examples
• ✔ Patterns that brought the API down from 40s → < 2s (95% improvement)

1. Presentation Logic Inside SQL (Anti-Pattern)

The stored procedure was formatting values inside SQL:

• Converting numbers to formatted strings
• Re-parsing those strings back
• Enforcing decimal precision instead of letting the app layer handle it

Why this is bad

SQL should return raw structured data, not formatted output.

❌ Bad SQL Example

SELECT 
    CONVERT(varchar(20), CAST(Amount AS DECIMAL(10,2))) AS AmountFormatted
FROM Transactions

✅ Better EF Core Example (Formatting in C#)

var result = await _context.Transactions
    .AsNoTracking()
    .Select(x => new 
    {
        x.Id,
        Amount = x.Amount,
        AmountFormatted = x.Amount.ToString("N2")
    })
    .ToListAsync();

2. Overuse of Table Variables

The SP used multiple table variables, acting like temporary caches for millions of rows.

Why this caused problems

• Table variables don't maintain statistics
• SQL Server assumes they return 1 row, leading to horrible query plans
• Pagination was done after loading everything into the table variable

❌ Bad SQL Pattern

DECLARE @t TABLE (...);

INSERT INTO @t
SELECT * FROM Transactions;

SELECT * FROM @t
ORDER BY CreatedOn
OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;

✅ EF Core Query Instead

var query = _context.Transactions.AsNoTracking();

var paged = await query
    .OrderByDescending(x => x.CreatedOn)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

Pagination happens before materializing any data.

3. Non-SARGable Filters (Silent Performance Killers)

The stored procedure had several filtering patterns that completely disabled index seeks, forcing SQL Server to scan the entire table.

A filter is SARGable (Search ARGument Able) only when SQL Server can use an index to locate matching rows efficiently.
When you wrap a column in a function or computation, SQL Server loses the ability to use the index.

Common Non-SARGable Patterns Found

1. Functions on Indexed Columns

WHERE CONVERT(VARCHAR(10), CreatedOn, 112) = @Date

This forces a full table scan, even if CreatedOn is indexed.

2. Wildcard on Both Sides

WHERE CustomerName LIKE '%' + @Name + '%'

No index can be used — SQL must scan every row.

3. Computed Values in the WHERE Clause

WHERE (Amount + Tax) = @Total

SQL Server cannot use the index on Amount or Tax.

✔ Correct SARGable Rewrites

1. Use Range Filters Instead of Converting Date Columns

❌ Bad

WHERE CONVERT(VARCHAR(10), CreatedOn, 112) = @Date

✅ Good

WHERE CreatedOn >= @StartDate
  AND CreatedOn < DATEADD(day, 1, @StartDate)

EF Core Equivalent:

query = query.Where(x =>
    x.CreatedOn >= request.StartDate &&
    x.CreatedOn < request.StartDate.AddDays(1));

2. Convert %term% Searches to Index-Friendly Patterns

If business logic allows prefix search:

EF Core:

query = query.Where(x => x.CustomerName.StartsWith(request.Name));

Generated SQL:

WHERE CustomerName LIKE @Name + '%'

→ Index seek friendly.

If full substring search %term% is required, consider Full-Text Search.

3. Avoid Computations on Column Values

❌ Bad

WHERE (Amount + Tax) = @Total

✅ Good

WHERE Amount = @Total - Tax

EF Core:

query = query.Where(x => x.Amount == request.Total - x.Tax);

4. Business Rules Hidden Inside SQL CASE Expressions

This was the part that initially scared me off.

The SP had dozens of embedded rules:

CASE 
    WHEN Status = 1 AND PaymentType = 'X' THEN 'Completed'
    WHEN Status = 2 AND PaymentType = 'Y' THEN 'Pending'
    ...
END AS PaymentStatus

Why this is bad

• Hard to understand
• Hard to change
• Logic is not testable
• Mixing data access & domain logic

✅ Refactored to C#

PaymentStatus = x.Status switch
{
    1 when x.PaymentType == "X" => "Completed",
    2 when x.PaymentType == "Y" => "Pending",
    _ => "Unknown"
};

Now business rules are readable, testable, and maintainable.

5. Pagination at the End of the SP

The SP collected thousands of rows, inserted them into a table variable, and then applied pagination.

❌ Bad Pattern

SELECT * FROM @temp ORDER BY CreatedOn
OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;

Why it's horribly inefficient

• SQL processed far more rows than needed
• Sorting is expensive
• No index support on table variables

✅ Correct Pattern (EF Core)

var paged = await _context.Transactions
    .Where(filter)
    .OrderByDescending(x => x.CreatedOn)
    .Skip(skip)
    .Take(take)
    .ToListAsync();

6. Monolithic Stored Procedure

The stored procedure had:

• 500+ lines
• Multiple responsibilities
• Hard-coded rules
• Nested queries
• Temporary structures everywhere

These SPs become untouchable over time.

Solution

Break it down:

• Data access → EF Core
• Business logic → C# services
• Formatting → Controller or frontend
• Heavy read-only queries → Optimized SQL / Dapper (if needed)

7. Missing Index Strategy

Even a perfect query fails without proper indexing.

Problems found:

• No index on CreatedOn
• No index on CustomerId
• No index on TransactionType

This caused:

• Full table scans
• Massive tempDB usage
• Slow joins
• Random performance degradation

Fix

CREATE INDEX IX_Transactions_CreatedOn 
    ON Transactions(CreatedOn);

CREATE INDEX IX_Transactions_CustomerId 
    ON Transactions(CustomerId);

Results were immediate.

My Refactoring Strategy

I decided on a two-step plan:

Step 1 — Migrate to EF Core Query (if possible)

Most filters and conditions translated well into EF Core. This alone removed table variables, CASE clutter, and pagination issues.

Step 2 — Optimize Stored Procedure & Use Dapper (if needed)

For the few heavy queries that EF couldn't optimize well, I rewrote the SQL cleanly and used Dapper for fast materialization.

Additional Improvements

• Created proper indexes
• Added in-memory caching for repeated internal queries
• Used proper pagination pattern
• Applied filtering before pagination
• Used AsNoTracking() everywhere for reporting queries

Final Result — 95% Performance Improvement

After implementing all fixes:

▶ Original API time: ~40 seconds
▶ After refactor: < 2 seconds

A massive 95% improvement.

I was proud.
And the codebase finally became maintainable again.

Conclusion

Legacy reporting stored procedures often grow into slow, over-engineered monsters. But with the right approach, you can turn a 40-second endpoint into a fast, clean API that responds in under two seconds.