慢查询优化求助：含UNION ALL的SQL语句运行时长超3小时

Hey there, let's dive into speeding up that query that's been running for over 3 hours. First, let's restate your original query clearly so we're on the same page:

SELECT * 
FROM stg.F_STG_ORCAMENTO F_STG_ORCAMENTO 
WHERE 1=1 
  AND F_STG_ORCAMENTO.COD_CONTA LIKE '6%' 
  AND F_STG_ORCAMENTO.COD_CONTA NOT IN (
    SELECT DISTINCT F_STGT_CNT_EXPLOR_CTB_GERAL.C_CNT_GERAL 
    FROM STG.F_STGT_CNT_EXPLOR_CTB_GERAL F_STGT_CNT_EXPLOR_CTB_GERAL 
    WHERE F_STGT_CNT_EXPLOR_CTB_GERAL.C_LOLOJA=F_STG_ORCAMENTO.C_CENT_CUSTO 
      AND INPUT(substr(put(F_STGT_CNT_EXPLOR_CTB_GERAL.DTA_CNT_EXPLOR,8.),1,4),4.)=F_STG_ORCAMENTO.DTA_ANO 
      AND INPUT(substr(put(F_STGT_CNT_EXPLOR_CTB_GERAL.DTA_CNT_EXPLOR,8.),5,2),2.)=F_STG_ORCAMENTO.DTA_MES 
      AND F_STGT_CNT_EXPLOR_CTB_GERAL.C_CNT_GERAL=F_STG_ORCAMENTO.COD_CONTA) 
  AND F_STG_ORCAMENTO.V_NUM_CUSTOS > 0 
UNION ALL 
SELECT * 
FROM stg.F_STG_ORCAMENTO F_STG_ORCAMENTO 
WHERE 1=1 
  AND F_STG_ORCAMENTO.COD_CONTA LIKE '94%' 
  AND F_STG_ORCAMENTO.COD_CONTA NOT IN (
    SELECT DISTINCT SUBSTR(F_STGT_CNT_EXPLOR_CTB_GERAL.C_CNT_ANL,4,9) 
    FROM STG.F_STGT_CNT_EXPLOR_CTB_GERAL F_STGT_CNT_EXPLOR_CTB_GERAL 
    WHERE F_STG_ORCAMENTO.C_CENT_CUSTO=F_STGT_CNT_EXPLOR_CTB_GERAL.C_LOLOJA 
      AND F_STG_ORCAMENTO.DTA_ANO=INPUT(substr(put(F_STGT_CNT_EXPLOR_CTB_GERAL.DTA_CNT_EXPLOR,8.),1,4),4.) 
      AND F_STG_ORCAMENTO.DTA_MES=INPUT(substr(put(F_STGT_CNT_EXPLOR_CTB_GERAL.DTA_CNT_EXPLOR,8.),5,2),2.) 
      AND SUBSTR(F_STGT_CNT_EXPLOR_CTB_GERAL.C_CNT_ANL,4,9)=F_STG_ORCAMENTO.COD_CONTA) 
  AND F_STG_ORCAMENTO.V_NUM_CUSTOS > 0

Key Issues Causing Slowness #

Let's break down why this query is dragging its feet:

• Dual full scans of the main table: You're using UNION ALL to scan stg.F_STG_ORCAMENTO twice, doubling the amount of work the database has to do.
• Function-heavy join conditions: Applying INPUT, substr, and put to DTA_CNT_EXPLOR in the WHERE clause blocks the database from using indexes on that column—forcing a full scan of the subquery table every time.
• Unnecessary DISTINCT in subqueries: NOT IN doesn't need DISTINCT; the database handles deduplication internally, and this extra clause adds unnecessary sorting/processing overhead.
• Correlated NOT IN subqueries: These often perform worse than alternative patterns like LEFT JOIN + IS NULL or NOT EXISTS, especially with large datasets.

Optimization Steps & Rewritten Query #

Here's how to fix these bottlenecks:

1. Combine the two main table scans

Instead of scanning F_STG_ORCAMENTO twice, use a single scan with an OR condition for COD_CONTA (since UNION ALL is just combining two separate filters).

2. Replace NOT IN with LEFT JOIN + IS NULL

This avoids the overhead of correlated subqueries and handles NULL values more reliably than NOT IN.

3. Precompute date conversions

Use a CTE to calculate the year/month from DTA_CNT_EXPLOR once, instead of repeating the function calls across multiple subqueries. If you have permission, consider adding persisted computed columns for these values to the table and indexing them for even better performance.

4. Remove redundant DISTINCT

As mentioned earlier, this clause adds no value here and just slows things down.

Rewritten Query Example

WITH cnt_explor_cte AS (
    SELECT 
        C_LOLOJA,
        INPUT(substr(put(DTA_CNT_EXPLOR,8.),1,4),4.) AS DTA_ANO,
        INPUT(substr(put(DTA_CNT_EXPLOR,8.),5,2),2.) AS DTA_MES,
        C_CNT_GERAL,
        SUBSTR(C_CNT_ANL,4,9) AS C_CNT_ANL_TRIMMED
    FROM STG.F_STGT_CNT_EXPLOR_CTB_GERAL
)
SELECT o.*
FROM stg.F_STG_ORCAMENTO o
LEFT JOIN cnt_explor_cte c1 
    ON o.C_CENT_CUSTO = c1.C_LOLOJA
    AND o.DTA_ANO = c1.DTA_ANO
    AND o.DTA_MES = c1.DTA_MES
    AND o.COD_CONTA = c1.C_CNT_GERAL
LEFT JOIN cnt_explor_cte c2
    ON o.C_CENT_CUSTO = c2.C_LOLOJA
    AND o.DTA_ANO = c2.DTA_ANO
    AND o.DTA_MES = c2.DTA_MES
    AND o.COD_CONTA = c2.C_CNT_ANL_TRIMMED
WHERE 
    (o.COD_CONTA LIKE '6%' OR o.COD_CONTA LIKE '94%')
    AND o.V_NUM_CUSTOS > 0
    -- Exclude rows that match either join condition
    AND c1.C_CNT_GERAL IS NULL
    AND c2.C_CNT_ANL_TRIMMED IS NULL

Additional Performance Boosts #

• Add targeted indexes:
  • On stg.F_STG_ORCAMENTO: Create a composite index on (COD_CONTA, V_NUM_CUSTOS, C_CENT_CUSTO, DTA_ANO, DTA_MES)—this covers all filter and join conditions, allowing the database to retrieve needed data directly from the index.
  • On STG.F_STGT_CNT_EXPLOR_CTB_GERAL: If you can't add computed columns, create an index on (C_LOLOJA, DTA_CNT_EXPLOR) including C_CNT_GERAL and C_CNT_ANL. If computed columns are an option, index (C_LOLOJA, DTA_ANO, DTA_MES) including the account columns.
• Update statistics: Make sure your database has up-to-date table and index statistics—outdated stats can lead the query optimizer to choose inefficient execution plans.

These changes should drastically cut down runtime by eliminating redundant scans, reducing function overhead, and letting the database use indexes effectively. Start by testing this rewritten query on a small subset of data to verify correctness before running it on the full dataset.

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