SQL Server Performance

Optimization of the query with variables in the where cluase

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by danny123, Apr 13, 2011.

  1. danny123 New Member

    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!
  2. Luis Martin Moderator

    With hard coded and variables are you testing in the same server?
  3. danny123 New Member

    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!
  4. Adriaan New Member

    Smells like the old parameter sniffing problem.
  5. danny123 New Member

    So is there any way to get away from this bad smell? :)
  6. Adriaan New Member

    Well, you could take the suggestion seriously, and do a search here on the forums for "parameter sniffing". Seriously. You'll be surprised.

Share This Page