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?
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.
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.
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)
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
OK,
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.
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.
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.