How I Turned a Half‑Hour SSRS Report into a Sub‑Second Query with a Stored Procedure

Recently I was optimizing a report for a group company. After optimization the query time dropped from over half an hour to seconds. I summarize the process to help others.

Data Background

The project is an MES deployment for Siemens China, running on production lines for over three years, accumulating massive data. The database contains more than five tables with over a hundred million rows, more than ten tables with tens of millions of rows, and many tables with millions of rows.

Below is the original SSRS report SQL from Siemens China developers:

select distinct b.MaterialID as matl_def_id, c.Descript,
case when right(b.MESOrderID, 12) < '001000000000' then right(b.MESOrderID, 9)
else right(b.MESOrderID, 12) end as pom_order_id,
 a.LotName, a.SourceLotName as ComLot, e.DefID as ComMaterials, e.Descript as ComMatDes,
 d.VendorID, d.DateCode, d.SNNote, b.OnPlantID, a.SNCUST
from (
    select m.lotname, m.sourcelotname, m.opetypeid, m.OperationDate, n.SNCUST
    from View1 m
    left join co_sn_link_customer n on n.SNMes=m.LotName
    where (m.LotName in (select val from fn_String_To_Table(@sn,',',1)) or (@sn) = '')
      and (m.sourcelotname in (select val from fn_String_To_Table(@BatchID,',',1)) or (@BatchID) = '')
      and (n.SNCust like '%' + @SN_ext + '%' or (@SN_ext) = '')
) a
left join (
    select * from Table1 where SNType = 'IntSN' and SNRuleName = 'ProductSNRule' and OnPlantID=@OnPlant
) b on b.SN = a.LotName
inner join MMdefinitions c on c.DefID = b.MaterialID
left join Table1 d on d.SN = a.SourceLotName
left join MMDefinitions e on e.DefID = d.MaterialID
where not exists (
    select distinct LotName, SourceLotName from ELCV_ASSEMBLE_OPS where LotName = a.SourceLotName and SourceLotName = a.LotName
) and (d.DateCode in (select val from fn_String_To_Table(@DCode,',',1)) or (@DCode) = ''
and (d.SNNote like '%' + @SNNote + '%' or (@SNNote) = '')
and (case when right(b.MESOrderID,12) < '001000000000' then right(b.MESOrderID,9) else right(b.MESOrderID,12) end) in (select val from fn_String_To_Table(@order_id,',',1)) or (@order_id) = ''
and (e.DefID in (select val from fn_String_To_Table(@comdef,',',1)) or (@comdef) = '');
--View1 is a nested view containing a table with over a hundred million rows and several tables with tens of millions of rows.
--Table1 contains more than 15 million rows.

The query could not return results in the B/S front end within half an hour, and even the SQL Analyzer timed out.

Exploration and Pitfalls

Indexes were missing; after adding them the speed barely improved.

Tables with tens of millions of rows lacked partitioning.

Directly partitioning the original tables caused deadlocks because the production line was still inserting data.

Attempting to partition without a clear query pattern (no date or sequential condition) was ineffective.

Analysis of the Original SQL

The WHERE clause contains many "@var IN … OR (@var = '')" patterns.

There are LIKE ‘%’+@var+‘%’ conditions.

CASE expressions are used.

Multiple joins to the same tables and nested views are present.

Optimization Design

First, rewrite the logic as a stored procedure for flexibility. The core idea is to create temporary tables based on the provided query parameters, update them incrementally, and finally join them to produce the result set.

Benefits of this approach include eliminating "= @var OR (@var = '')" checks, removing dynamic SQL concatenation, and improving readability.

Stored Procedure

/**
 * 某某跟踪报表
 **/ --exec spName1 '','','','','','','公司代号'
CREATE Procedure spName1
    @MESOrderID nvarchar(320), --工单号,最多30个
    @LotName nvarchar(700),   --产品序列号,最多50个
    @DateCode nvarchar(500),  --供应商批次号,最多30个
    @BatchID nvarchar(700),   --组装件序列号/物料批号,最多50个
    @comdef nvarchar(700),    --组装件物料编码,最多30个
    @SNCust nvarchar(1600),   --外部序列号,最多50个
    @OnPlant nvarchar(20)     --平台
AS
BEGIN
    SET NOCOUNT ON;
    -- 1) Define global temporary table #FinalLotName
    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);
        -- further logic ...
    END
    -- additional parameter handling blocks ...
    -- 2) Define global temporary table #FinalCO_SN
    CREATE TABLE #FinalCO_SN (
        SN NVARCHAR(50),
        SourceSN NVARCHAR(50),
        SNCust NVARCHAR(128),
        matl_def_id NVARCHAR(50),
        ComMaterials NVARCHAR(50),
        MESOrderID NVARCHAR(20),
        OnPlantID NVARCHAR(20),
        VendorID NVARCHAR(20),
        DateCode NVARCHAR(20),
        SNNote NVARCHAR(512)
    );
    -- further processing ...
    IF EXISTS (SELECT 1 FROM #FinalLotName)
    BEGIN
        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,
               OnPlantID,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 DISTINCT SN, SourceSN FROM #FinalCO_SN x WHERE x.SN = a.SourceSN AND x.SourceSN = a.SN);
    END
    ELSE
    BEGIN
        PRINT 'There is no queryable condition, please enter at least a query condition.';
    END
END
GO

Conclusion

Many developers write poorly performing SQL in a hurry. Writing reliable, high‑performance SQL is not difficult; the challenge is forming good habits. The key points are to avoid full table scans, use proper indexes, write clear SQL, and consider WITH (NOLOCK) when dirty reads are acceptable.