CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Victor Rodrigues
Victor Rodrigues

Reputation: 11711

Why SQL Server go slow when using variables?

I have a sql query that runs super fast, around one second, when not using variables, like:

WHERE id BETWEEN 5461094 and 5461097

But when I have:

declare @firstId int
declare @lastId int

set @firstId = 5461094
set @lastId = 5461097

...
    WHERE id BETWEEN @firstId and @lastId

... the query runs really slow, finishing only after some minutes. Why does it happen? I need to use variables. Can I do any improvement to avoid this performance problems?

Upvotes: 23

Views: 21756

Answers (7)

Tamas Gervai
Tamas Gervai

Reputation:

Actually, it was answered very well, I just write here a workaround as it worked for me:

Create a stored procedure with the SQL

WHERE id BETWEEN @firstId and @lastId

After then call the stored proc with parameters @firstId and @lastId and it will speed up. I still not 100% why it is working, but it works.

Upvotes: 0

Sam Saffron
Sam Saffron

Reputation: 131112

It seems like this query is relating to a stored procedure, its execution plan will get compiled first time the proc is executed and then reused for subsequent executions.

Its possible that the compiled plan is really bad for situations where firstid is really close to lastid, however its really good when the values are far apart.

Try enabling the WITH RECOMPILE option on your stored proc. If it resolves the issue and you are happy with the proc being recompiled every time its executed (you will get a performance hit) leave it there. If you are still unhappy with the performance consider re-architecting the proc so it does not need the recompilation.

Upvotes: 1

Mark Brady
Mark Brady

Reputation:

OK,

  1. You are the Optimizer and the Query Plan is a vehicle.
  2. I will give you a query and you have to choose the vehicle.
  3. All the books in the library have a sequential number

My query is Go to the library and get me all the books between 3 and 5

You'd pick a bike right, quick, cheap, efficient and big enough to carry back 3 books.

New query.

Go to the library and get all the books between @x and @y.

Pick the vehicle.

Go ahead.

That's what happens. Do you pick a dump truck in case I ask for books between 1 and Maxvalue? That's overkill if x=3 and y=5. SQL has to pick the plan before it sees the numbers.

Upvotes: 28

HLGEM
HLGEM

Reputation: 96552

If those variables are input variables for a stored proc, you could be running into the issue of parameter sniffing. http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Upvotes: 3

DiGi
DiGi

Reputation: 2558

Funny is that this code will be fast too:

DECLARE @sql VARCHAR(8000)

SET @sql = 'SELECT * FROM table_x WHERE id BETWEEN ' + CAST(@firstId AS VARCHAR) + ' AND ' + CAST(@lastId AS VARCHAR)

EXEC (@sql)

(MSSQL 2000)

Upvotes: 3

Robert Wagner
Robert Wagner

Reputation: 17793

Is ID in an Index (eg Primary Key)? If not, try adding one.

Another thing could be that in the first (fast) instance the query is getting executed slightly differently. The most common thing I've seen happen is that the joins get done in an inefficient order. Try re-ordering the joins, or converting some to subqueries. If you post more of your query we can assist further.

Upvotes: 0

Ben R
Ben R

Reputation: 431

It's because when the values are hard coded, it can look up the statistics it has on the data in the table, and figure out the best query to run. Have a look at the execution plans of each of these queries. It must be scanning when your using variables.

if the range is always small you might be able to use an index hint to help this.

Upvotes: 13

Related Questions