优化关联大量记录的SQL查询：缩短执行时长且结果一致

Let’s tackle this query performance issue step by step—there are several concrete changes we can make to cut down execution time without altering your final results. Here’s what I’d recommend:

1. Fix Dynamic SQL Parameterization (Avoid String Concatenation) #

Your current query uses string concatenation to pass parameters, which prevents the AS400 query optimizer from reusing execution plans and can lead to inefficient processing. Instead, use parameterized dynamic SQL with the USING clause to pass variables safely and efficiently—this also eliminates the risk of accidental syntax errors from string formatting.

2. Replace Unnecessary LEFT JOINs with INNER JOINs #

Your WHERE clause filters on whsoh = @warehouse, a field from orderhp. Since a LEFT JOIN would return NULL values for whsoh when there’s no matching orderhp record, those rows would be excluded by the WHERE condition anyway. Changing this to an INNER JOIN reduces the number of rows the database has to process upfront, which is a quick win for performance.

3. Pre-Aggregate the Audit Table First #

The Audit table is your largest dataset—instead of joining the full table to orderhp and ordercnhp before grouping, pre-aggregate the Audit data by KEYVADD first. This shrinks the dataset size drastically before you do any joins, which cuts down on the work the database needs to do for associations.

4. Create Targeted Indexes #

Indexing is critical for large tables. For your query, create these indexes to speed up filtering, grouping, and joins:

• On the Audit table:

  CREATE INDEX IX_Audit_IMGTADD_KEYVADD ON Audit(IMGTADD, KEYVADD, VALUADD, timestmp);
  -- If your AS400 supports function-based indexes, add one for the LEFT(KEYVADD,7) join:
  CREATE INDEX IX_Audit_KEYVADD_Prefix ON Audit(LEFT(KEYVADD, 7), IMGTADD);
  

• On orderhp:

  CREATE INDEX IX_orderhp_ONHOH_WHSOH ON orderhp(ONHOH, whsoh, preoh, nmdoh);
  

• On ordercnhp:

  CREATE INDEX IX_ordercnhp_ONHCN_SCSCN ON ordercnhp(ONHCN, scscn);
  

After creating indexes, run RUNSTATS on all tables to update the database’s statistics—this helps the optimizer make better decisions about index usage.

5. Refactor the Query Logic (Simplify HAVING Conditions) #

Your HAVING clause is complex—we can simplify it by moving the pre-aggregation into a CTE or subquery, then applying filters after joining with the order tables. Here’s a rewritten version incorporating all the above changes:

DECLARE @paramdate DATETIME, @warehouse INT;
SET @warehouse = 711;
SET @paramdate = '2018-05-17 12:00:00.000';

EXEC('
WITH AuditAgg AS (
    SELECT 
        KEYVADD,
        MIN(CASE WHEN VALUADD=0 THEN timestmp END) AS Status0,
        MIN(CASE WHEN VALUADD=2 THEN timestmp END) AS Status2,
        MIN(CASE WHEN VALUADD=4 THEN timestmp END) AS Status4,
        MIN(CASE WHEN VALUADD=5 THEN timestmp END) AS Status5,
        MIN(CASE WHEN VALUADD=7 THEN timestmp END) AS Status7,
        MIN(CASE WHEN VALUADD=8 THEN timestmp END) AS Status8,
        MIN(CASE WHEN VALUADD=9 THEN timestmp END) AS Status9
    FROM Audit
    WHERE IMGTADD = ''A''
    GROUP BY KEYVADD
)
SELECT 
    aa.KEYVADD,
    aa.Status0,
    aa.Status2,
    aa.Status4,
    aa.Status5,
    aa.Status7,
    aa.Status8,
    aa.Status9,
    MIN(h.nmdoh) AS Customer,
    MIN(c.scscn) AS Container,
    MIN(h.whsoh) AS Warehouse,
    MIN(h.preoh) AS Preorder
FROM AuditAgg aa
INNER JOIN orderhp h ON LEFT(aa.KEYVADD,7) = h.ONHOH
INNER JOIN ordercnhp c ON h.onhoh = c.onhcn
WHERE h.whsoh = ?
GROUP BY aa.KEYVADD, aa.Status0, aa.Status2, aa.Status4, aa.Status5, aa.Status7, aa.Status8, aa.Status9
HAVING 
    aa.Status2 <= ?
    AND (
        (MIN(h.preoh) = ''Y'' AND (aa.Status4 IS NOT NULL OR aa.Status5 IS NOT NULL OR aa.Status7 IS NOT NULL OR aa.Status8 IS NOT NULL OR aa.Status9 IS NOT NULL))
        OR MIN(h.preoh) = ''N''
    )
    AND (
        (aa.Status7 IS NULL AND aa.Status8 IS NULL AND aa.Status9 IS NULL)
        OR aa.Status7 >= ?
        OR aa.Status8 >= ?
        OR aa.Status9 >= ?
    )
') AT IBMAS400 USING @warehouse, @paramdate, @paramdate, @paramdate, @paramdate;

Key Improvements in This Rewrite: #

• The AuditAgg CTE pre-aggregates the Audit table, reducing the dataset size before joining.
• Parameterized USING clause passes variables without error-prone string concatenation.
• Unnecessary LEFT JOINs are replaced with INNER JOINs to filter rows early.
• HAVING conditions are simplified by reusing pre-aggregated values from the CTE, avoiding redundant calculations.

Final Tips #

• After implementing these changes, check the query execution plan (using EXPLAIN on AS400) to confirm indexes are being used as expected.
• If LEFT(KEYVADD,7) is a common join condition, consider adding a computed column to the Audit table storing this value permanently, then index that column—this eliminates the function call in the join, which can slow down index usage.

内容的提问来源于stack exchange，提问作者rabeery