Why Your SSRS Report Is Slowing Down: Real-World SQL Optimization Lessons

Data Background

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

(Historical issue: the database performance was never monitored. I’m new here…)

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 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 containing a table with over a hundred million rows and several tables with tens of millions.

-- Table1 holds more than 15 million rows.

The query runs for half an hour to an hour in the B/S front‑end and even the SQL Analyzer cannot finish within reasonable time because it scans the huge tables.

Analysis shows missing indexes and lack of partitioning on the large tables. Adding indexes alone did not help; the tables also lacked proper partitioning.

Attempting to partition the existing tables caused deadlocks because the production line continuously writes data, so the partition operation locked the tables.

The proposed solution is to create an empty table, define partitions on it, then copy data over, but this approach was a misstep because the query has no selective predicates (date, ID) that benefit from partitioning.

1. Analysis of the Original SQL

Many WHERE clauses use patterns like @var in … or (@var = '').

Frequent LIKE '%'+@var+'%' conditions.

Use of CASE … END expressions.

Multiple joins to the same tables and nested view usage that may be unnecessary.

Use of * in SELECT statements.

2. Optimization Design

Rewrite the logic as a stored procedure to improve flexibility.

The core idea: for each supplied filter create a temporary table, insert matching rows, and finally aggregate results from these temporary tables. This avoids full‑table scans and reduces the need for IN / NOT IN constructs.

Benefits:

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

Removes dynamic SQL concatenation, improving readability.

When writing the stored procedure, keep in mind:

Prefer temporary tables over full scans.

Replace IN/NOT IN with EXISTS/NOT EXISTS.

Confirm whether fuzzy LIKE searches are truly needed; remove them if not.

Create appropriate indexes for the workload.

Avoid * in SELECT lists.

Do not apply functions to columns in WHERE clauses.

If real‑time results are not required, allow dirty reads with WITH (NOLOCK) to prevent blocking.

3. Stored Procedure

For the full implementation, see the original article (link omitted due to length).

After replacing the SSRS report with this stored procedure, the query runs in seconds in the analyzer and 1–2 seconds in the B/S front‑end.

4. Conclusion

Performance‑critical SQL should avoid full‑table scans, use proper indexing, and write clear, maintainable statements. When tables are updated during query windows and dirty reads are acceptable, WITH (NOLOCK) can further reduce contention.