SQL Query Optimization for Large-Scale MES Reporting Using Stored Procedures

Data Background

The project is a Siemens China MES deployment that runs on a production line for more than three years, accumulating billions of rows across several tables (5 tables > 1 billion rows, >10 tables > 10 million rows, many tables > 1 million rows). The original SSRS report query took more than half an hour (or timed out) because it performed full scans on these massive tables.

Exploration and Pitfalls

Initial attempts focused on adding missing indexes, but query speed did not improve significantly. The next idea was to partition the large tables, but partitioning directly on the live production tables caused deadlocks because the production line continuously writes data. Creating a new empty table, building partitions on it, and copying data seemed viable, yet the business logic (tracing product and serial numbers) had no regular filter conditions (e.g., date or sequential IDs), making partitioning ineffective and even detrimental.

Analysis of the Original SQL

WHERE clauses contain many @var IN (…) OR (@var = '') patterns.

Frequent use of LIKE '%'+@var+'%' leading to scans.

Use of CASE … END expressions inside predicates.

Multiple joins to the same view/table, some of which may be unnecessary.

Complex nested views that hide the real table sizes.

The query essentially performs full scans on a >1 billion‑row table and three >10 million‑row tables, which explains the extreme latency.

Optimization Design

The core idea is to replace the monolithic query with a stored procedure that builds temporary result sets based on the provided filter parameters. This approach offers several benefits:

Eliminates repetitive = @var OR (@var = '') checks.

Removes dynamic SQL concatenation, improving readability and safety.

Uses temporary tables to replace full scans with targeted look‑ups.

Replaces IN / NOT IN with EXISTS / NOT EXISTS .

Removes unnecessary LIKE clauses when fuzzy search is not required.

Applies appropriate indexes for the specific query paths.

Allows dirty reads with WITH (NOLOCK) for non‑critical reporting queries.

The stored procedure creates a global temporary table #FinalLotName (and later #FinalCO_SN ) and populates it step‑by‑step based on which input parameters are supplied. Each block checks for a non‑empty parameter, splits the comma‑separated list into a temporary table via fn_String_To_Table , and joins the relevant base tables to collect the needed columns.

CREATE Procedure spName1
    @MESOrderID nvarchar(320),
    @LotName nvarchar(700),
    @DateCode nvarchar(500),
    @BatchID nvarchar(700),
    @comdef nvarchar(700),
    @SNCust nvarchar(1600),
    @OnPlant nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;
    -- Define temporary tables and populate them based on supplied parameters
    CREATE TABLE #FinalLotName (LotName NVARCHAR(50), SourceLotName NVARCHAR(50), SNCust NVARCHAR(128));
    IF @LotName <> ''
    BEGIN
        SELECT Val INTO #WorkLot FROM fn_String_To_Table(@LotName,',',1);
        SELECT LotPK,LotName INTO #WorkLotPK FROM MMLots WITH(NOLOCK) WHERE EXISTS (SELECT 1 FROM #WorkLot b WHERE b.Val = MMLots.LotID);
        -- Additional joins and inserts omitted for brevity
    END
    -- Similar blocks for @BatchID, @SNCust, @DateCode, @comdef, etc.
    -- Final join to produce the report result set
    SELECT a.matl_def_id, b.Descript, a.MESOrderID AS pom_order_id, a.SN AS LotName,
           a.SourceSN AS ComLot, a.ComMaterials, c.Descript AS ComMatDes,
           a.VendorID, a.DateCode, a.SNNote, a.OnPlantID, a.SNCust
    FROM #FinalCO_SN a
    JOIN MMDefinitions b WITH(NOLOCK) ON a.matl_def_id = b.DefID
    JOIN MMDefinitions c WITH(NOLOCK) ON a.ComMaterials = c.DefID
    WHERE NOT EXISTS (SELECT 1 FROM #FinalCO_SN x WHERE x.SN = a.SourceSN AND x.SourceSN = a.SN);
END
GO

After replacing the SSRS report with this stored procedure, the query runs in seconds in SQL Server Management Studio, and the front‑end B/S application reports results within 1–2 seconds.

Conclusion

Performance problems often stem from writing overly complex, unindexed SQL that forces full table scans. The key take‑aways are to avoid full scans, use proper indexes, rewrite logic with temporary tables and stored procedures, and consider WITH (NOLOCK) when dirty reads are acceptable. These practices turn a half‑hour query into a sub‑second operation.