SQL语句性能优化求助：结果正确但运行效率低下

Hey there! Let's work through optimizing that slow SQL query you've got. It's great you're getting the right results, but performance issues with large datasets like 2.8 million rows in AD3IM are super common—especially when you're just starting out with SQL. Here are actionable steps to speed things up:

1. Add Targeted Indexes #

Indexes are your biggest ally for speeding up queries on large tables. Right now, your tables only have primary key indexes, which don't cover the filters and joins you're using. Let's fix that:

• AD3IM Table: You're filtering on COMPANY and CALMONTH, plus joining to PMATERIAL on MATERIAL. A composite index including these fields lets the database jump straight to relevant rows instead of scanning all 2.8 million records:

  CREATE INDEX IDX_AD3IM_COMP_CAL_MAT ON AD3IM (COMPANY, CALMONTH, MATERIAL);
  

• PMATERIAL Table: You're joining on MATERIAL and filtering on OBJVERS = 'A' plus specific MATTYPE values. This composite index will accelerate both the join and filter operations:

  CREATE INDEX IDX_PMAT_MAT_OBJ_MATTYPE ON PMATERIAL (MATERIAL, OBJVERS, MATTYPE);
  

  Quick critical check: You mentioned PMATERIAL's primary key is CMATERIAL OBJVERS—is the join field supposed to be b~CMATERIAL instead of b~MATERIAL? If that's a typo, fix it immediately; mismatched join fields will destroy performance and might even return incorrect data.

• PCOMPANY Table: This table is small (1.4k rows), so it's less critical. But since its primary key is COMPANY OBJVERS, the existing PK index already covers your join and filter needs here. Just double-check the join logic is correct.

2. Simplify Filter Logic #

Replace your OR condition with an IN clause. It's cleaner, more readable, and most database optimizers handle IN more efficiently than multiple OR statements:

-- Replace this:
(b~MATTYPE = @constant_O OR b~MATTYPE = @constant_T)

-- With this:
b~MATTYPE IN (@constant_O, @constant_T)

3. Check for Data Type Mismatches #

Ensure the data types of your join fields match exactly (e.g., a~MATERIAL and b~MATERIAL are the same type). Implicit data conversions (like converting strings to numbers) slow down joins and prevent indexes from being used.

4. Analyze the Execution Plan #

Use your SQL tool's execution plan feature to pinpoint bottlenecks. Look for phrases like "Full Table Scan" on AD3IM or PMATERIAL—these mean your indexes aren't being utilized. The plan will show you where the query is spending most of its time, so you can target optimizations precisely.

5. Optional: Table Partitioning #

If AD3IM keeps growing, consider partitioning it by CALMONTH or COMPANY. Partitioning splits the table into smaller chunks, so the query only scans the partitions that match your filter criteria. This is a bigger change, so you might need to collaborate with your DBA to set it up.

Modified Query with Simplified Filter #

Here's your query with the OR replaced by IN—a quick win for readability and potential performance:

SELECT 
    c~GROUP, 
    c~CONTI, 
    c~REGION, 
    c~AREA, 
    c~CNTRY, 
    a~COMPANY, 
    b~MATTYPE, 
    a~CALMONTH, 
    a~LOC_CURRCY, 
    a~BASE_UOM, 
    SUM(ZVALSTCKV) AS amount, 
    SUM(ZVALSTCKQ) AS quantity 
INTO TABLE @data_raw1 
FROM AD3IM AS a 
INNER JOIN PMATERIAL AS b 
    ON a~MATERIAL = b~MATERIAL 
INNER JOIN PCOMPANY AS c 
    ON a~COMPANY = c~COMPANY 
WHERE 
    a~COMPANY IN @rangettable_COMPANY 
    AND a~CALMONTH IN @rangetable_CALMONTH 
    AND b~MATTYPE IN (@constant_O, @constant_T) 
    AND b~OBJVERS = 'A' 
    AND c~OBJVERS = 'A' 
GROUP BY 
    a~COMPANY, 
    b~MATTYPE, 
    a~CALMONTH, 
    a~LOC_CURRCY, 
    a~BASE_UOM, 
    c~GROUP, 
    c~CONTI, 
    c~REGION, 
    c~AREA, 
    c~CNTRY

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