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 cluase

Hi 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?
Hi Martin,
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’
update cdbP
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
commit tran
Set XACT_ABORT OFF
Thanks!

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.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |