Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Posts
    22

    Unanswered: performance Issue with input variables

    Can anybody tell me the perfomance difference for 2 sql statements below:

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    When you use variables, the query optimizer does not know how far apart the values are, and so opts for a table scan. To force it to the index, you can use an index hint.

  3. #3
    Join Date
    May 2005
    Posts
    22
    I checked the execution plan, it shows me that it is using the index seek. But still it is taking more time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •