Optimization of the query with variables in the where cluase | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Optimization of the query with variables in the where cluaseHi there,
In one of my query,when i am test and providing the hard coded values in the where close for the filter, it finishes the query in less than 1 second. But in real stored procedure, those values in the where cluase are coming from variables declared with in the same stored procedure. Like @batchid and all and with all this, its taking more than 8 seconds to finish the query.
Why is this happening? and what can i do to fix it?
Thanks alot in advance!
With hard coded and variables are you testing in the same server?
Thanks for the response. This is what we are trying to do.This is not the whole stored procedure but it should give you good idea. Below query is part of stored procedure with all the @bi and @sc as the input parameter and @ac is the variable and its value we are getting from another query within the stored procedure.
Set XACT_ABORT ON
Begin distributed tran
declare @ac varchar(10)
Set @ac = ‘XXX’
Set attr_code = BUP.attr_code
from FSPDB.DBName1.dbo.Table1 cdbP, DBName2.dbo.Table2 BUP
where BUP.source_code = @sc and BUP.BatchID = @bi
and cdbP.source_code = BUP.source_code
Set XACT_ABORT OFF
Smells like the old parameter sniffing problem.
So is there any way to get away from this bad smell?
Well, you could take the suggestion seriously, and do a search here on the forums for "parameter sniffing". Seriously. You’ll be surprised.