Optimizing Large-Scale SQL Queries with Stored Procedures and Indexing

After struggling with an SSRS report that took over half an hour to return results due to full scans on tables containing billions of rows, the author rewrote the query as a stored procedure, introducing temporary tables, conditional logic, and proper indexing to reduce execution time to seconds.

The optimization process began with a thorough analysis of the original SQL, which contained many WHERE @var IN (…) OR @var = '' patterns, LIKE '%' + @var + '%' clauses, and unnecessary CASE expressions. The author also identified missing indexes and the lack of table partitioning on multi‑million‑row tables.

Key steps included:

Creating appropriate indexes on frequently filtered columns.

Replacing costly IN / NOT IN checks with EXISTS subqueries.

Eliminating wildcard LIKE searches when they were not required.

Using temporary tables to break down complex joins and avoid repeated full scans.

Applying WITH (NOLOCK) for read‑only data where dirty reads are acceptable.

The final stored procedure ( CREATE PROCEDURE spName1 … ) builds several temporary tables ( #FinalLotName , #FinalCO_SN , etc.) to progressively filter data based on the provided parameters ( @MESOrderID , @LotName , @BatchID , @SNCust , @DateCode , @comdef ). Conditional branches ensure that only the necessary tables are scanned, and the procedure handles special cases such as different plant codes.

After deploying the stored procedure, the report query time dropped from half an hour to a few seconds on the B/S front‑end, demonstrating the substantial performance gains achievable through careful SQL refactoring and database design.

The article concludes with practical advice: always avoid full table scans, prioritize proper indexing, write clear and maintainable SQL, and consider using WITH (NOLOCK) when business requirements permit dirty reads.