|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, April 14, 2009 3:10 PM
Points: 8,
Visits: 28
|
|
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
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: 2 days ago @ 3:08 AM
Points: 685,
Visits: 93
|
|
did u use a query execution plan to see where the query is taking its time?
Everything you can imagine is real.
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Thursday, October 22, 2009 5:53 AM
Points: 894,
Visits: 318
|
|
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,
Crispin
I can't die, there are too many people who still have to meet me!
It's not a bug, SQL just misunderstood me!
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, November 04, 2009 7:10 PM
Points: 1,383,
Visits: 1,952
|
|
Investigate parameter sniffing. Almost certainly to blame here. If this is in a sproc, consider the WITH RECOMPILE option.
Best,
TheSQLGuru
SQL Server MVP
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Thursday, November 05, 2009 2:33 PM
Points: 70,
Visits: 213
|
|
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, September 30, 2009 2:41 PM
Points: 1,008,
Visits: 232
|
|
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
*****************/
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Thursday, November 05, 2009 2:33 PM
Points: 70,
Visits: 213
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 12:01 AM
Points: 14,524,
Visits: 10,413
|
|
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://www.sqlservercentral.com/Forums/Topic744625-360-1.aspx
Please direct replies to that thread. Thank you
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, November 05, 2009 3:19 PM
Points: 39,
Visits: 229
|
|
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
|
|
|
|
