Optimizing Large‑Scale MES Reporting Queries: Indexing, Partitioning, and Stored Procedure Refactoring

The author describes a massive MES reporting project for Siemens China where several tables contain hundreds of millions to tens of millions of rows, leading to a SQL query that cannot finish within reasonable time when run from the B/S front‑end.

The original query (shown below) performs full table scans on a billion‑row table and multiple ten‑million‑row tables, uses many OR (@var='') patterns, LIKE with wildcards, and even selects * from views, resulting in extremely slow performance.

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 as 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 as c on c.DefID = b.MaterialID
left join Table1 as d on d.SN = a.SourceLotName
inner join MMDefinitions as 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 two‑level nested view (actual name omitted) containing a billion‑row table and several ten‑million‑row tables.
--Table1 holds more than 15 million rows.

Key problems identified:

Missing or incomplete indexes on the large tables.

No table partitioning for the tens‑of‑millions‑row tables.

Use of OR (@var='') patterns and wildcard LIKE that prevent index usage.

Selection of * from views, causing unnecessary column reads.

Functions applied to columns in the WHERE clause, forcing scans.

Optimization design proposes rewriting the logic as a stored procedure that builds one or more temporary tables based on the supplied parameters, thereby eliminating the need for repetitive OR checks and reducing full scans. Additional recommendations include:

Replace IN/NOT IN with EXISTS/NOT EXISTS.

Remove unnecessary LIKE clauses after confirming they are not required.

Create appropriate, query‑specific indexes.

Avoid using * and instead select only needed columns.

For reports that tolerate stale data, use WITH (NOLOCK) to avoid blocking.

The author also warns against naïvely partitioning the original tables while they are still receiving data, as this can cause deadlocks; instead, a new empty table should be created, partitioned, and then populated.

After implementing the stored procedure and the suggested indexing/partitioning changes, the query execution time dropped from hours to seconds in SQL Server Management Studio, and the B/S front‑end now returns results within 1–2 seconds.

In summary, the article emphasizes the importance of proper indexing, avoiding full‑table scans, writing clean SQL without unnecessary dynamic fragments, and using temporary tables or stored procedures to handle complex, optional filter criteria efficiently.