Post reply

performance Issue with input variables

  • July 30, 2007 at 9:44 am

    #174004

    select count(*) from products where prodID between 20987 and 21003

    go

    declare @ProdID1 int

    declare @prodID2 int

    set @ProdID1 = 20987

    set @prodID2 = 21003

    select count(*) from products where prodID between @ProdID1 and @prodID2

    First statement takes 1 second to execute and second statement takes more than one minute. I don't understand what is the problem with using variables.

    Thanks

    Kiran

  • July 30, 2007 at 10:10 am

    #723351

    did u use a query execution plan to see where the query is taking its time?

    Everything you can imagine is real.

  • July 31, 2007 at 1:39 am

    #723489

    I would say it is doing an implicit conversion on the data types as the column is not a int. Because of this, all indexes are ignored. The first query, SQL is converting 1234 to the same as the column. The second, it is forced to an int then having to do a second conversion to the actual column data type. When this happens, you cannot use an index and a scan will occur.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 1, 2007 at 8:56 am

    #723899

    Investigate parameter sniffing.  Almost certainly to blame here.  If this is in a sproc, consider the WITH RECOMPILE option.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 22, 2009 at 10:32 am

    #1013440

    Hi Sir ,

    I am new to SQL server .. I am trying to optimize one store procedure

    the problem is when i use EXEC store procedure , i am seeing table scan in execution plan

    but when i declare the varibles and passthe parameters directly inti the query .. i didn't see any table scan ...

    what is the issue , pleae help me regarding this .. because of the tabelscan this store procedures runs very slow .....

    thanks in advance

  • June 22, 2009 at 12:04 pm

    #1013496

    What is the data type of the Products.ProdID column?

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • June 30, 2009 at 10:22 am

    #1017741

    Hi Sir ,

    Please help me !!

    I am trying to optimize one store procedure

    The problem is when

    I use EXEC store procedure , i am seeing table scan in execution plan

    but when i declare the varibles and pass the parameters directly in

    the query .. i didn't see any table scan all i see is index Seek ...

    what should be the Issuse , please help me regarding this .. because of the tabelscan this store procedures runs very slow .....

    Thanks in advance

  • June 30, 2009 at 1:32 pm

    #1017909

    Hi Sir ,

    Please help me !!

    I am trying to optimize one store procedure

    The problem is when

    I use EXEC store procedure , i am seeing table scan in execution plan

    but when i declare the varibles and pass the parameters directly in

    the query .. i didn't see any table scan all i see is index Seek ...

    what should be the Issuse , please help me regarding this .. because of the tabelscan this store procedures runs very slow .....

    Thanks in advance

    praveen

    Also posted here: http://qa.sqlservercentral.com/Forums/Topic744625-360-1.aspx

    Please direct replies to that thread. Thank you

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 1, 2009 at 7:52 am

    #1018410

    It sounds like parameter sniffing. If you don't want to specify WITH RECOMPILE, you can also declare local variables and then reassign your input parameters to the local variables. Then SQL Server knows the value of the variables when it determines what path to take. If you're only using the input parameters, SQL Server doesn't know their value at execution time, thus you get the table scan.

    input parameter coming into stored proc @ProdID1

    then in stored proc....

    declare @ProdID1 int

    declare @localProdID1 int

    set @localProdID1 = @ProdID1

    and then use @localProdID1 in your where clause

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply