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

The author describes a recent effort to optimize a Siemens China MES reporting system that originally required half an hour or more to return results because the underlying SQL performed full scans on tables containing billions of rows.

Data Background

The MES database holds several tables with over a hundred million rows and many more with tens of millions of rows, leading to severe performance bottlenecks when queried directly from the B/S front‑end.

Exploration and Pitfalls

Initial attempts focused on adding missing indexes, but query speed did not improve because the queries still performed full scans on massive tables. The author also considered table partitioning, but realized that without a regular filtering condition (e.g., date or sequential ID) partitioning would be ineffective and could even degrade performance.

Analysis of the Original SQL

The original statement contains numerous WHERE @var IN (…) OR (@var = '') clauses, LIKE '%'+@var+'%' patterns, and CASE … END expressions, as well as multiple joins to the same large tables and nested views, all of which contribute to the full‑table scans.

Optimization Design

The solution rewrites the logic as a stored procedure that builds temporary tables based on the provided filter parameters, replaces IN/NOT IN with EXISTS/NOT EXISTS , removes unnecessary * selections, and adds WITH (NOLOCK) where dirty reads are acceptable. Indexes are created on the relevant columns, and the procedure avoids function calls on indexed fields.

/**
 * 某某跟踪报表
 **/
--exec spName1 '','','','','','','公司代号'
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, process each filter, and assemble final result set
    CREATE TABLE #FinalLotName (
        LotName NVARCHAR(50),
        SourceLotName NVARCHAR(50),
        SNCust NVARCHAR(128)
    );
    -- Example of handling @LotName filter
    IF @LotName <> ''
    BEGIN
        SELECT Val INTO #WorkLot FROM fn_String_To_Table(@LotName,',',1);
        -- further processing ...
    END
    -- Additional filter handling (BatchID, SNCust, etc.) follows the same pattern
    -- Final join to retrieve required fields
    SELECT a.LotName, a.SourceLotName, a.SNCust, b.MaterialID, c.ComMaterials,
           b.MESOrderID, b.OnPlantID, c.VendorID, c.DateCode, c.SNNote
    FROM #FinalLotName a
    LEFT JOIN #WorkCO_SN1 b ON a.LotName = b.SN
    LEFT JOIN #SourceLotNameTable c ON a.SourceLotName = c.SourceLotName;
END;
GO

Result

After replacing the SSRS report with this stored procedure, the query execution time dropped from tens of minutes to a few seconds on the B/S front‑end, demonstrating the substantial performance gains achievable through careful SQL analysis and procedural refactoring.

Conclusion

Key takeaways include avoiding full‑table scans, using proper indexing, preferring EXISTS over IN , eliminating unnecessary wildcard searches, and, when appropriate, allowing dirty reads with WITH (NOLOCK) to prevent blocking during data updates.