Sql-server – TSQL Why is Top faster with a variable

sql servert-sql

Morning all,

I have been working on some moderately complex sql to 'obtain' some data from a third party products database, to display it in our own in house applications.

I added in a select to obtain the top record from a table within a subquery (if thats makes sense)

the query took nearly 3minutes to return a final result set of 100 records using

SELECT TOP 1 ...

I had a look online for improvements to what I was trying to achieve and it was suggested that I change my select to use a variable, as below

DECLARE @topCount INT
SET @topCount = 1

SELECT TOP (@topCount) ...

This took the same query from the 3minutes down to 1second, which is great!

But can anyone explain why this is so.

Best Answer

When you do top 1 the query optimizer will build a plan that is built to fetch 1 row as fast as possible.

When you use a local variable the value of the variable is unknown to the optimizer and instead builds a plan that is optimized to fetch 100 rows as fast as possible.

In your case the query plan generated with a row goal of 100 is the better plan to use even when you only want one row.

To verify you can try to add option (recompile) to your query with the variable. In that case SQL Server will use the current value of @topCount as a row goal and since that is 1 you should get the slow plan.

Related Solutions

Sql-server – TempDB Version Store cleanup

^{(Note: this isn't a 100% certain answer, and I don't have references, so let me know if you can prove otherwise.)}

The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously. That simply wouldn't work if "old" versions within one database were cleared out in the middle of another database's snapshot transaction.

So, if there's a very old open transaction, the version store will not be able to clear until that transaction either commits or rolls back.

Sql-server – Why are multiple COUNT faster than one SUM with CASE

The COUNT(*) version is able to simply seek into the index you have on the status column once for each status you are selecting, whereas the SUM(...) version needs to seek the index twelve times (the total number of unique status types).

Clearly seeking an index three times is going to be faster than seeking it 12 times.

The first plan requires a memory grant of 238MB, whereas the second plan requires a memory grant of 650MB. It may be that the larger memory grant could not be immediately filled, making the query that much slower.

Alter the second query to be:

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),
        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),
        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)
FROM dbo.Claims c
WHERE c.Status = 'Approved'
    OR c.Status = 'Valid'
    OR c.Status = 'Reject';

This will allow the query optimizer to eliminate 75% of the index seeks, and should result in both a lower required memory grant, lower I/O requirements, and faster time-to-result.

The SUM(CASE WHEN ...) construct essentially prevents the query optimizer from pushing the Status predicates down into the index seek portion of the plan.

Best Answer

Related Solutions

Sql-server – TempDB Version Store cleanup

Sql-server – Why are multiple COUNT faster than one SUM with CASE

Related Question