[Solved] Slow query with many joins - expanding the magic pill?

EverSQL Database Performance Knowledge Base

Slow query with many joins - expanding the magic pill?

I have a query that takes 20 minutes to run, even though I have an index for every column in the where clauses, and every column being joined:

SELECT DISTINCT skt.VCDRAWING_REG_NO, skb.NDRAWING_ORG_NO, skb.NDRAWING_ORG_REV_NO, skb.CAPPLY_START_DATE, skb.CAPPLY_END_DATE, skto.*
FROM SPM_ABS_TRANBASE skt
JOIN SPM_ABS_BASE skb
ON skt.NDRAWING_ORG_REV_NO = skb.NDRAWING_ORG_REV_NO
AND skt.NDRAWING_ORG_NO = skb.NDRAWING_ORG_NO
JOIN SPM_ABS_MODEL skm
ON skb.NDRAWING_ORG_REV_NO = skm.NDRAWING_ORG_REV_NO
AND skb.NDRAWING_ORG_NO = skm.NDRAWING_ORG_NO
JOIN SPM_ABS_TRANOPT skto
ON skt.NDRAWING_SYSTEM_NO = skto.NDRAWING_SYSTEM_NO
JOIN ModelImport mi
ON skm.CMODEL = mi.ModelCode
WHERE (skb.CAPPLY_START_DATE <= DATEADD(day, 2, GETDATE()) OR skb.CAPPLY_START_DATE IS NULL)
AND (skb.CAPPLY_END_DATE >= DATEADD(day, -2, GETDATE()) OR skb.CAPPLY_END_DATE IS NULL)

Here is my query plan.

One thing that puzzles me is this: If I add the following WHERE clause, the query returns in about 0.5 seconds:

AND mi.ModelCode = '3FBK5'

Now you're saying, well, duh, of course it gets much faster with that - the thing is, the ModelImport table contains only 351 records. Which means if I were to split up the query above into 351 queries, each with its own where clause for a distinct ModelCode - then I can get 100% of my query results in about 175 seconds, or 2.9 minutes. This is dramatically faster. Which tells me that something in the wide-open query is grossly inefficient, and the query plan is bad.

Here is my query plan with AND mi.ModelCode = '3FBK5' added.

After viewing my query plan, any ideas how I can speed this up?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Calling Functions With Indexed Columns (query line: 26): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `day` is indexed, the index won’t be used as it’s wrapped with the function `DATEADD`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  2. Avoid Calling Functions With Indexed Columns (query line: 30): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `day` is indexed, the index won’t be used as it’s wrapped with the function `DATEADD`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  3. Avoid Selecting Unnecessary Columns (query line: 7): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        DISTINCT skt.VCDRAWING_REG_NO,
        skb.NDRAWING_ORG_NO,
        skb.NDRAWING_ORG_REV_NO,
        skb.CAPPLY_START_DATE,
        skb.CAPPLY_END_DATE,
        skto.* 
    FROM
        SPM_ABS_TRANBASE skt 
    JOIN
        SPM_ABS_BASE skb 
            ON skt.NDRAWING_ORG_REV_NO = skb.NDRAWING_ORG_REV_NO 
            AND skt.NDRAWING_ORG_NO = skb.NDRAWING_ORG_NO 
    JOIN
        SPM_ABS_MODEL skm 
            ON skb.NDRAWING_ORG_REV_NO = skm.NDRAWING_ORG_REV_NO 
            AND skb.NDRAWING_ORG_NO = skm.NDRAWING_ORG_NO 
    JOIN
        SPM_ABS_TRANOPT skto 
            ON skt.NDRAWING_SYSTEM_NO = skto.NDRAWING_SYSTEM_NO 
    JOIN
        ModelImport mi 
            ON skm.CMODEL = mi.ModelCode 
    WHERE
        (
            skb.CAPPLY_START_DATE <= DATEADD(day, 2, GETDATE()) 
            OR skb.CAPPLY_START_DATE IS NULL
        ) 
        AND (
            skb.CAPPLY_END_DATE >= DATEADD(day, -2, GETDATE()) 
            OR skb.CAPPLY_END_DATE IS NULL
        )

Related Articles



* original question posted on StackOverflow here.