Query performance with/without condition in predicate

Question

Need help in understanding performance differences in query execution with and without additional condition in predicate. The query without predicate (InventTable.DATAAREAID = 'DAT'):

USE ecc_wrk;
    SELECT 'Артикул'=InventTable.ITEMID,
           'Артикул/Цвет'=InventTable.ITEMID+CASE
                                               WHEN isnull( InventColor.INVENTCOLORID, '' )<>''
                                               THEN '/'+isnull( InventColor.INVENTCOLORID, '' )
                                               ELSE ''
                                             END,
           'Размер'=isnull( INVENTSIZE.INVENTSIZEID, '' ),
           'Номенклатурная группа'=InventTable.ITEMGROUPID,
           'Гендер'=InventTable.ECC_GENDERID,
           'Уценённый товар'=InventTable.ECC_ISMARKEDDOWN,
           'Ставка НДС'=TaxData.TAXVALUE
    FROM InventTable
    JOIN
    InventTableModule
    ON InventTableModule.DATAAREAID='DAT'
       AND InventTableModule.ITEMID=InventTable.ITEMID
       AND InventTableModule.MODULETYPE=0 -- Модуль запасы
        JOIN
        INVENTITEMLOCATION
        ON INVENTITEMLOCATION.DATAAREAID='DAT'
           AND INVENTITEMLOCATION.ITEMID=InventTable.ITEMID
           AND INVENTITEMLOCATION.INVENTDIMID='AllBlank'
            JOIN
            INVENTITEMINVENTSETUP
            ON INVENTITEMINVENTSETUP.DATAAREAID='DAT'
               AND INVENTITEMINVENTSETUP.ITEMID=InventTable.ITEMID
               AND INVENTITEMINVENTSETUP.INVENTDIMID='AllBlank'
               AND INVENTITEMINVENTSETUP.STOPPED=0
                LEFT JOIN
                InventColor
                ON InventColor.DATAAREAID='DAT'
                   AND InventColor.ITEMID=InventTable.ITEMID
                    LEFT JOIN
                    INVENTSIZE
                    ON InventSize.DATAAREAID='DAT'
                       AND InventSize.ITEMID=InventTable.ITEMID
                        LEFT JOIN
                        ECC_FEACCTABLE
                        ON ECC_FEACCTABLE.DATAAREAID='DAT'
                           AND ECC_FEACCTable.FEACCId=CASE
                                                        WHEN isnull( InventSize.ECC_FEACCID, '' )<>''
                                                        THEN InventSize.ECC_FEACCID
                                                        ELSE InventTable.ECC_FEACCID
                                                      END
                            LEFT JOIN
    (
      SELECT TAXONITEM_Ranked.DATAAREAID,
             TAXONITEM_Ranked.TAXITEMGROUP,
             TAXONITEM_Ranked.TAXCODE
      FROM
      (
        SELECT DATAAREAID,
               TAXONITEM.TAXITEMGROUP,
               TAXONITEM.TAXCODE,
               TAXONITEM.RECID,
               RANK( ) OVER( PARTITION BY DATAAREAID,
                                          TAXONITEM.TAXITEMGROUP ORDER BY TAXONITEM.RECID DESC ) 'Rank'
        FROM TAXONITEM
        WHERE TaxOnItem.DATAAREAID='DAT'
      ) TAXONITEM_Ranked
      WHERE TAXONITEM_Ranked.Rank=1
    ) TAXONITEM_Dist
                            ON TAXONITEM_Dist.DATAAREAID='DAT'
                               AND TAXONITEM_Dist.TaxItemGroup=ECC_FEACCTABLE.TAXITEMGROUPSALES -- Налоговые ставки:
                                LEFT JOIN
                                TaxData
                                ON TaxData.DATAAREAID='DAT'
                                   AND TaxData.TaxCode=TAXONITEM_Dist.TAXCODE
                                   AND (GETDATE( ) BETWEEN TaxData.TAXFROMDATE AND TaxData.TAXTODATE
                                        OR TaxData.TAXFROMDATE=''
                                        AND TaxData.TAXTODATE='')
    WHERE
    --InventTable.DATAAREAID = 'DAT' and 
    (InventTable.ITEMTYPE=0
     OR InventTable.ITEMTYPE=1
        AND InventTable.ECC_ISMARKEDDOWN=1);

IO and Time stats:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 764 ms, elapsed time = 968 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(454197 row(s) affected)
Table 'Worktable'. Scan count 21952, logical reads 400161, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTSIZE'. Scan count 1, logical reads 2105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTITEMLOCATION'. Scan count 0, logical reads 201289, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTABLE'. Scan count 1, logical reads 35570, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTABLEMODULE'. Scan count 1, logical reads 28361, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTITEMINVENTSETUP'. Scan count 1, logical reads 11989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ECC_FEACCTABLE'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAXDATA'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAXONITEM'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTCOLOR'. Scan count 1, logical reads 571, physical reads 0, read-ahead reads 187, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3651 ms,  elapsed time = 6523 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The plan is here

The query with additional predicate look like this (InventTable.DATAAREAID = 'DAT'):

USE ecc_wrk;

SELECT 'Артикул'=InventTable.ITEMID,
       'Артикул/Цвет'=InventTable.ITEMID+CASE
                                           WHEN isnull( InventColor.INVENTCOLORID, '' )<>''
                                           THEN '/'+isnull( InventColor.INVENTCOLORID, '' )
                                           ELSE ''
                                         END,
       'Размер'=isnull( INVENTSIZE.INVENTSIZEID, '' ),
       'Номенклатурная группа'=InventTable.ITEMGROUPID,
       'Гендер'=InventTable.ECC_GENDERID,
       'Уценённый товар'=InventTable.ECC_ISMARKEDDOWN,
       'Ставка НДС'=TaxData.TAXVALUE
FROM InventTable
JOIN
InventTableModule
ON InventTableModule.DATAAREAID='DAT'
   AND InventTableModule.ITEMID=InventTable.ITEMID
   AND InventTableModule.MODULETYPE=0 -- Модуль запасы
    JOIN
    INVENTITEMLOCATION
    ON INVENTITEMLOCATION.DATAAREAID='DAT'
       AND INVENTITEMLOCATION.ITEMID=InventTable.ITEMID
       AND INVENTITEMLOCATION.INVENTDIMID='AllBlank'
        JOIN
        INVENTITEMINVENTSETUP
        ON INVENTITEMINVENTSETUP.DATAAREAID='DAT'
           AND INVENTITEMINVENTSETUP.ITEMID=InventTable.ITEMID
           AND INVENTITEMINVENTSETUP.INVENTDIMID='AllBlank'
           AND INVENTITEMINVENTSETUP.STOPPED=0
            LEFT JOIN
            InventColor
            ON InventColor.DATAAREAID='DAT'
               AND InventColor.ITEMID=InventTable.ITEMID
                LEFT JOIN
                INVENTSIZE
                ON InventSize.DATAAREAID='DAT'
                   AND InventSize.ITEMID=InventTable.ITEMID
                    LEFT JOIN
                    ECC_FEACCTABLE
                    ON ECC_FEACCTABLE.DATAAREAID='DAT'
                       AND ECC_FEACCTable.FEACCId=CASE
                                                    WHEN isnull( InventSize.ECC_FEACCID, '' )<>''
                                                    THEN InventSize.ECC_FEACCID
                                                    ELSE InventTable.ECC_FEACCID
                                                  END
                        LEFT JOIN
(
  SELECT TAXONITEM_Ranked.DATAAREAID,
         TAXONITEM_Ranked.TAXITEMGROUP,
         TAXONITEM_Ranked.TAXCODE
  FROM
  (
    SELECT DATAAREAID,
           TAXONITEM.TAXITEMGROUP,
           TAXONITEM.TAXCODE,
           TAXONITEM.RECID,
           RANK( ) OVER( PARTITION BY DATAAREAID,
                                      TAXONITEM.TAXITEMGROUP ORDER BY TAXONITEM.RECID DESC ) 'Rank'
    FROM TAXONITEM
    WHERE TaxOnItem.DATAAREAID='DAT'
  ) TAXONITEM_Ranked
  WHERE TAXONITEM_Ranked.Rank=1
) TAXONITEM_Dist
                        ON TAXONITEM_Dist.DATAAREAID='DAT'
                           AND TAXONITEM_Dist.TaxItemGroup=ECC_FEACCTABLE.TAXITEMGROUPSALES -- Налоговые ставки:
                            LEFT JOIN
                            TaxData
                            ON TaxData.DATAAREAID='DAT'
                               AND TaxData.TaxCode=TAXONITEM_Dist.TAXCODE
                               AND (GETDATE( ) BETWEEN TaxData.TAXFROMDATE AND TaxData.TAXTODATE
                                    OR TaxData.TAXFROMDATE=''
                                    AND TaxData.TAXTODATE='')
WHERE
InventTable.DATAAREAID = 'DAT' and 
(InventTable.ITEMTYPE=0
 OR InventTable.ITEMTYPE=1
    AND InventTable.ECC_ISMARKEDDOWN=1);

The IO and Time stats:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 856 ms, elapsed time = 856 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(454197 row(s) affected)
Table 'Worktable'. Scan count 17154, logical reads 384377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTCOLOR'. Scan count 1, logical reads 1068, physical reads 0, read-ahead reads 259, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTSIZE'. Scan count 1, logical reads 2105, physical reads 0, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTITEMLOCATION'. Scan count 0, logical reads 201289, physical reads 0, read-ahead reads 9118, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTABLE'. Scan count 1, logical reads 35570, physical reads 0, read-ahead reads 56, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTABLEMODULE'. Scan count 1, logical reads 28361, physical reads 0, read-ahead reads 212, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTITEMINVENTSETUP'. Scan count 1, logical reads 11989, physical reads 0, read-ahead reads 301, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ECC_FEACCTABLE'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 41, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAXDATA'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAXONITEM'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 4555 ms,  elapsed time = 8963 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The plan is here

The clustered index on InventTable looks like this:

Please, bear in mind that the only value in DATAAREAID column is 'DAT'.

As we can see the variant without additional filtering works a bit faster as to CPU and Elapsed times. The question is - why?

My quess is that in case of using additional InventTable.DATAAREAID = 'DAT' the optimizer uses Index Seek operator on InventTable with additional filtering (Seek Predicate) and it just goes slower than Scan with conditions on ItemType and IsMarkedDown.

Help will be deeply appreciated.

Erik Darling · Accepted Answer · 2017-10-13 12:47:53Z

This is going to straddle the line between comment and answer -- sorry about that.

There are some important differences between the plan aside from just the seek vs scan.

Memory grants
- For the fast plan: MemoryGrant="72304"
- For the slow plan: MemoryGrant="2176"

This is (presumably) to support the Sort operator needed to use a Merge Join between INVENTTABLE and INVENTTABLEMODULE

This is because the Clustered Index Scan of INVENTTABLE has the Ordered: False property, while the seek is Ordered: True.

I'm guessing that a residual effect of the added memory grant is join order choice. If you look closely, INVENTCOLOR moves to a very different place in the plan.

Fast plan:

Slow plan:

Which brings us to the next important difference: Merge operator properties!

In both plans, there's a Merge operator with the Many to Many: True attribute.

via The Blogger I Miss The Most, Craig Freedman:

Merge join can also support many-to-many merge joins. In this case, we must keep a copy of each row from input 2 whenever we join two rows. This way, if we later find that we have a duplicate row from input 1, we can play back the saved rows. On the other hand, if we find that the next row from input 1 is not a duplicate, we can discard the saved rows. We save these rows in a worktable in tempdb. The amount of disk space we need depends on the number of duplicates in input 2.

In the fast plan, the Many to Many Merge Join is on 183k rows. In the slow plan, it's on 454k rows. The difference between 18 and 50 MB of data.

There are some other, smaller differences, but these stick out to me.

You could try a FORCE ORDER hint generally, or a HASH JOIN hint on INVENTCOLOR specifically. You could also try a FORCESCAN hint on INVENTTABLE.

Hope this helps!

thank you! How do I understand that the amount of pages from WorkTable is greater in the fast plan? — George K, Oct 13, 2017 at 13:12

Stack Exchange Network

Query performance with/without condition in predicate

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
performance-tuning
or ask your own question.

Hot Network Questions

Query performance with/without condition in predicate

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serverperformanceperformance-tuning or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
performance-tuning
or ask your own question.