CODEWITHSUNDEEP
sql-serverperformancevariablesselect
user3608009
user3608009

Reputation: 21

In SQL Server query slows dramatically when variable in SELECT clause

I have the following simple code which I run in SQL Server Management Studio:

DECLARE @CurDeviceIndex  int
SET @CurDeviceIndex = 314

SELECT TOP 1 
    DeviceIndex, 
    DatetimeOccurred 
FROM 
    [dvm_data].[dbo].[CalculatedData] 
WHERE 
    DeviceIndex = @CurDeviceIndex 
ORDER BY 
    ID DESC

In some cases this query takes forever to run, actually I never waited until the end. When I instead of variable put the DeviceIndex directly in the query it executes instantly:

SELECT TOP 1 
    DeviceIndex, 
    DatetimeOccurred 
FROM 
    [dvm_data].[dbo].[CalculatedData] 
WHERE 
    DeviceIndex = 314 
ORDER BY 
    ID DESC

It seems that execution takes forever when 1) variable is used, and 2) the DeviceIndex is such that query returns nothing. When query returns something, then also version with variable seems to be working instantly.

When I use hard coded DeviceIndex, query returns every time instantly, whether there are result or not.

Any ideas what might be causing this strange behavior?!

Upvotes: 1

Views: 93

Answers (1)

James Z
James Z

Reputation: 12317

Since you're using variables in management studio, SQL Server doesn't know the value of it when you run it and has to optimize the plan for an unknown value.

If you use the same clause inside a procedure, it will know the value and optimize for it -- but this happens with the first call, and the rest of the executions will use the same plan (unless a recompile happens or the plan gets thrown out of the cache).

When you use the value in the SQL directly, the statement will be optimized for that exact value.

If you want to know why it takes a lot longer, you'll have to look at the query plan. Most likely there is a scan in one of the plan and a seek in the other. You can see the values of the parameters used to create the plan from the properties of the leftmost object in the plan.

Upvotes: 1

Related Questions