SQL Server – How Table Variable Improves Query Performance

performancequery-performancesql serversql server 2014table variabletemporary-tables

for this specific case, that I will try to explain below, using a table variable is performing better than not using a table variable.

I would like to know why, and if possible, get rid of the table variable.

this is the query using the table variable:

USE [BISource_UAT]
GO

set statistics io on
SET STATISTICS TIME ON

    SET NOCOUNT ON;

    DECLARE @OrderStartDate DATETIME = '15-feb-2015'
    DECLARE @OrderEndDate DATETIME = '28-feb-2016'
    DECLARE @tmp TABLE
    (
    strBxOrderNo VARCHAR(20)
    ,sintReturnId INT
    )  

    INSERT INTO @tmp
    SELECT  strBxOrderNo
            ,sintReturnId
    FROM    TABLEBACKUPS.dbo.tblBReturnHistory rh
    WHERE   rh.sintReturnStatusId in ( 3 )
    AND     rh.dtmAdded >= @OrderStartDate
    AND     rh.dtmAdded < @OrderEndDate

    SELECT 
             op.lngPaymentID
            ,op.strBxOrderNo
            ,op.sintPaymentTypeID
            ,op.strCurrencyCode
            ,op.strBCCurrencyCode
            ,op.decPaymentAmount
            ,op.decBCPaymentAmount
            ,ap.strAccountCode
            ,o.sintMarketID
            ,o.sintOrderChannelID
            ,o.sintOrderTypeID
            ,CASE   WHEN opgv.lngpaymentID IS NULL THEN NULL
                     -- Not a Voucher = Null
                WHEN gvp.strIssuedBxOrderNo IS NULL THEN 0 ELSE 1 
              END AS [IsPromoVoucher] -- Is a Voucher - check type
            ,o.sdtmOrdCreated

    FROM    @tmp rh

            INNER JOIN TABLEBACKUPS.dbo.tblBReturn r 
                    ON r.sintReturnId = rh.sintReturnId 
                   AND r.strBxOrderNo = rh.strBxOrderNo

            INNER JOIN bocss2.dbo.tblBOrder o 
                    ON o.strBxOrderNo = r.strBxOrderNo

            INNER JOIN Bocss2.dbo.tblBOrderPayment op 
                    ON op.strBxOrderNo = o.strBxOrderNo

            INNER JOIN TABLEBACKUPS.dbo.tblBOrderItemReturn AS oir 
                    ON r.sintReturnId = oir.sintReturnID 
                   AND r.strBxOrderNo = oir.strBxOrderNo

            INNER JOIN Bocss2.dbo.tblBOrderItem AS i 
                    ON i.strBxOrderNo = oir.strBxOrderNo 
                   AND i.sintOrderSeqNo = oir.sintOrderSeqNo

            INNER JOIN TABLEBACKUPS.dbo.tblBAccountParticipant ap 
                   ON o.lngAccountParticipantID = ap.lngParticipantID

            LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBOrderPaymentGiftVoucher opgv 
                         ON op.lngPaymentID = opgv.lngPaymentID

            LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucher gv 
                         ON opgv.strVoucherNumber = gv.strVoucherNumber

            LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucherPromotion gvp 
                         ON gvp.strIssuedBxOrderNo = gv.strIssuedBxOrderNo

    WHERE   oir.decReturnFinalAmount > 0
    AND     o.sdtmOrdCreated >= @OrderStartDate

this produces the following 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 = 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 = 78 ms, elapsed time = 86 ms.

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

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

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

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

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#BF0B2154'. Scan count 0, logical reads 1957, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturnHistory'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 9 ms.
Table 'tblBGiftVoucherPromotion'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBGiftVoucher'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPaymentGiftVoucher'. Scan count 0, logical reads 452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItem'. Scan count 0, logical reads 904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPayment'. Scan count 186, logical reads 1649, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBAccountParticipant'. Scan count 0, logical reads 7112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrder'. Scan count 3557, logical reads 14267, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItemReturn'. Scan count 1951, logical reads 5865, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturn'. Scan count 0, logical reads 3902, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BF0B2154'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 138 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.

using showplan_text on I would like to show the query plan:

first part of the query – populating the table variable

second part of the query:
using the table varible and joining the other tables:

This is the XML plan of the query using table variable.

now this is the same query NOT using a table variable:

USE [BISource_UAT]
GO

set statistics io on
SET STATISTICS TIME ON

    SET NOCOUNT ON;

    DECLARE @OrderStartDate DATETIME = '15-feb-2015'
    DECLARE @OrderEndDate DATETIME = '28-feb-2016'

    SELECT 
             op.lngPaymentID
            ,op.strBxOrderNo
            ,op.sintPaymentTypeID
            ,op.strCurrencyCode
            ,op.strBCCurrencyCode
            ,op.decPaymentAmount
            ,op.decBCPaymentAmount
            ,ap.strAccountCode
            ,o.sintMarketID
            ,o.sintOrderChannelID
            ,o.sintOrderTypeID
            ,CASE   WHEN opgv.lngpaymentID IS NULL 
               THEN NULL -- Not a Voucher = Null
                WHEN gvp.strIssuedBxOrderNo IS NULL 
                THEN 0 ELSE 1 END AS [IsPromoVoucher] 
                -- Is a Voucher - check type
            ,o.sdtmOrdCreated

    FROM    TABLEBACKUPS.dbo.tblBReturnHistory rh

            INNER JOIN TABLEBACKUPS.dbo.tblBReturn r 
                    ON r.sintReturnId = rh.sintReturnId 
                   AND r.strBxOrderNo = rh.strBxOrderNo

            INNER JOIN bocss2.dbo.tblBOrder o 
                    ON o.strBxOrderNo = r.strBxOrderNo
                   AND o.sdtmOrdCreated >= @OrderStartDate

            INNER JOIN Bocss2.dbo.tblBOrderPayment op 
                    ON op.strBxOrderNo = o.strBxOrderNo

            INNER JOIN TABLEBACKUPS.dbo.tblBOrderItemReturn AS oir 
                    ON r.sintReturnId = oir.sintReturnID 
                   AND r.strBxOrderNo = oir.strBxOrderNo
                   AND oir.decReturnFinalAmount > 0

            INNER JOIN Bocss2.dbo.tblBOrderItem AS i 
                    ON i.strBxOrderNo = oir.strBxOrderNo 
                   AND i.sintOrderSeqNo = oir.sintOrderSeqNo

            INNER JOIN TABLEBACKUPS.dbo.tblBAccountParticipant ap 
                   ON o.lngAccountParticipantID = ap.lngParticipantID

            LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBOrderPaymentGiftVoucher opgv 
                         ON op.lngPaymentID = opgv.lngPaymentID

            LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucher gv 
                         ON opgv.strVoucherNumber = gv.strVoucherNumber

            LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucherPromotion gvp 
                         ON gvp.strIssuedBxOrderNo = gv.strIssuedBxOrderNo

    WHERE   rh.sintReturnStatusId in ( 3 )
    AND     rh.dtmAdded >= @OrderStartDate
    AND     rh.dtmAdded < @OrderEndDate

when having a look at the stats, this is what we have got:

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 = 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 = 0 ms, elapsed time = 0 ms.

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

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

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

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

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBGiftVoucher'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBAccountParticipant'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturn'. Scan count 1, logical reads 170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItemReturn'. Scan count 0, logical reads 35849, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPayment'. Scan count 9408, logical reads 87643, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItem'. Scan count 1950, logical reads 8336, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrder'. Scan count 1951, logical reads 7835, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturnHistory'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPaymentGiftVoucher'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBGiftVoucherPromotion'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 625 ms,  elapsed time = 612 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.

Now, regarding the execution plan in text format:

setting the parameters

Now the important part, running the show:

This is the XML plan of the query NOT using table variable.

But how come using the table variable I got less reads, less I/O, and the execution (without clearing the cache) has always been faster?

I can provide any create table script, or anything else necessary for better understanding of this situation.

just post any comment here and I will reply.

this is a similar question:

Why is using a table variable more than twice as fast as a #temp table in this specific case?

when running the queries after CHECKPOINT; DBCC DROPCLEANBUFFERS;
the results were:

query with table variable

query without table variable

Best Answer

The main factors in play here are:

The optimizer does not try to find the best plan; its goal is to find a reasonable plan quickly
It assumes the query will be run with a cold cache
The cost model used favours sequential I/O over random I/O
Repeated seeks into an index are assumed to be randomly distributed

The cardinality estimate for a table variable is 1 row (unless a statement level recompile occurs, or trace flag 2453 is active). This low estimate results in a very low-cost plan, featuring a navigational strategy based on nested loops. This plan may continue to be effective for relatively low row counts, especially if the data needed does not need to be read in from persistent storage.

With more accurate cardinality estimates, the optimizer favours a plan using hash joins and a few scans. This appears to be cheaper than a navigational strategy, given the assumptions listed above; especially concerning the cold cache, and the relatively low cost of a sequential scan compared with many seeks (assuming a largely random I/O pattern).

The table variable plan may be slower than the alternative if the data needed is not in memory - or it may not. The cost model is exactly that - a model - the exact numbers used may not be representative of your hardware and configuration, and the assumptions made may not be valid in the particular circumstances.

All these caveats apply especially to low cost queries (which both are) since small cost changes can produce very different plan shapes. In fact, both plans are successful in that they produce results quickly and efficiently enough.

Subqueries in CASE expressions

Consider the following (perfectly legal) query:

DECLARE @Base AS TABLE (a integer NULL);
DECLARE @When AS TABLE (b integer NULL);
DECLARE @Then AS TABLE (c integer NULL);
DECLARE @Else AS TABLE (d integer NULL);

SELECT
    CASE
        WHEN (SELECT W.b FROM @When AS W) = 1
            THEN (SELECT T.c FROM @Then AS T)
        ELSE (SELECT E.d FROM @Else AS E)
    END
FROM @Base AS B;

The semantics of CASE are that WHEN/ELSE clauses are generally evaluated in textual order. In the query above, it would be incorrect for SQL Server to return an error if the ELSE subquery returned more than one row, if the WHEN clause was satisfied. To respect these semantics, the optimizer produces a plan that uses pass-through predicates:

Pass-through predicates

The inner side of the nested loop joins are only evaluated when the pass-through predicate returns false. The overall effect is that CASE expressions are tested in order, and subqueries are only evaluated if no previous expression was satisfied.

CASE expressions with an EXISTS subquery

Where a CASE subquery uses EXISTS, the logical existence test is implemented as a semi-join, but rows that would normally be rejected by the semi-join have to be retained in case a later clause needs them. Rows flowing through this special kind of semi-join acquire a flag to indicate if the semi-join found a match or not. This flag is known as the probe column.

The details of the implementation is that the logical subquery is replaced by a correlated join ('apply') with a probe column. The work is performed by a simplification rule in the query optimizer called RemoveSubqInPrj (remove subquery in projection). We can see the details using trace flag 8606:

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Part of the input tree showing the EXISTS test is shown below:

ScaOp_Exists 
    LogOp_Project
        LogOp_Select
            LogOp_Get TBL: #T2
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier [T2].ID
                ScaOp_Identifier [T1].ID

This is transformed by RemoveSubqInPrj to a structure headed by:

LogOp_Apply (x_jtLeftSemi probe PROBE:COL: Expr1008)

This is the left semi-join apply with probe described previously. This initial transformation is the only one available in SQL Server query optimizers to date, and compilation will simply fail if this transformation is disabled.

One of the possible execution plan shapes for this query is a direct implementation of that logical structure:

NLJ Semi Join with Probe

The final Compute Scalar evaluates the result of the CASE expression using the probe column value:

Compute Scalar expression

The basic shape of the plan tree is preserved when the optimize considers other physical join types for the semi join. Only merge join supports a probe column, so a hash semi join, though logically possible, is not considered:

Merge with probe column

Notice the merge outputs an expression labelled Expr1008 (that the name is the same as before is a coincidence) though no definition for it appears on any operator in the plan. This is just the probe column again. As before, the final Compute Scalar uses this probe value to evaluate the CASE.

The problem is that the optimizer doesn't fully explore alternatives that only become worthwhile with merge (or hash) semi join. In the nested loops plan, there is no advantage to checking if rows in T2 match the range on every iteration. With a merge or hash plan, this could be a useful optimization.

If we add a matching BETWEEN predicate to T2 in the query, all that happens is that this check is performed for each row as a residual on the merge semi join (tough to spot in the execution plan, but it is there):

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
            AND T2.ID BETWEEN 5000 AND 7000 -- New
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

Residual predicate

We would hope that the BETWEEN predicate would instead be pushed down to T2 resulting in a seek. Normally, the optimizer would consider doing this (even without the extra predicate in the query). It recognizes implied predicates (BETWEEN on T1 and the join predicate between T1 and T2 together imply the BETWEEN on T2) without them being present in the original query text. Unfortunately, the apply-probe pattern means this is not explored.

There are ways to write the query to produce seeks on both inputs to a merge semi join. One way involves writing the query in quite an unnatural way (defeating the reason I generally prefer EXISTS):

WITH T2 AS
(
    SELECT TOP (9223372036854775807) * 
    FROM #T2 AS T2 
    WHERE ID BETWEEN 5000 AND 7000
)
SELECT 
    T1.ID, 
    DoesExist = 
        CASE 
            WHEN EXISTS 
            (
                SELECT * FROM T2 
                WHERE T2.ID = T1.ID
            ) THEN 1 ELSE 0 END
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

TOP trick plan

I wouldn't be happy writing that query in a production environment, it's just to demonstrate that the desired plan shape is possible. If the real query you need to write uses CASE in this particular way, and performance suffers by there not being a seek on the probe side of a merge semi-join, you might consider writing the query using different syntax that produces the right results and a more efficient execution plan.

Sql-server – Which of these queries is best for performance

Sometimes I wonder if SHORT scripts really is the best thing to focus on.

The size of a script has little to do with how efficiently the query will execute. A more compact statement will likely consume fewer resources in terms of compilation, but (re)compilation is usually a rare occurrence in a live system.

Fewer table accesses is usually desirable, though, and this does lead to more compact code.

Very generally speaking, a smaller execution plan will yield better results, and a lower estimated cost will yield better results. Again, though, it's highly situational. Cost estimates in particular can be way off in some cases. It's important to measure the actual execution time, because at the end of the day, that's what matters.

With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method?

First of all, we need to know how much data will be in these tables in a real system. Right now there's so little it will be difficult to use the STATISTICS TIME performance metrics to figure out a winner -- the results that come back will be dominated by factors other than the query execution. With more data, it's likely the plans will change, thus rendering the comparison here moot.

Having said that, by looking at the query plans as they are now from a logical point of view, the first one is the winner.

You can see that the Clustered Index Scan of quantities appears once in the first plan, while it appears four times in the second one. The second plan also contains an expensive Distinct Sort as a result of using UNIONs (this operator could be eliminated by using UNION ALLs instead, which won't change the results).

The first query could also probably be improved, by getting index seeks on the colors and sizes tables, instead of table scans. It might be worth trying a hash match plan as well (which is what you'll probably see when quantities and products are larger), but for tables this small, the startup cost may be too much overhead to be of benefit.

What I would suggest you do is run each of the statements you want to test 10,000+ times in a loop, figure out the average execution time, and then compare.

Best Answer

Related Solutions

Sql-server – Check existence with EXISTS outperform COUNT! … Not

Subqueries in CASE expressions

CASE expressions with an EXISTS subquery

Sql-server – Which of these queries is best for performance

Related Question