| Author |
Topic |
|
janis
Starting Member
7 Posts |
 Posted - 2009-07-07 : 06:26:56
|
| I have a table with more than 10 000 000 records. Table has about 15 columns and 5 indexes. One of index is on one varchar column. That column is varchar(125) and when I run query:select * from tableT where varcharColumn='sdjdsf123klj129asdd933ed324d'I got result in less than second.BUT if i run this query:declare @v varcahr(125)set @v='sdjdsf123klj129asdd933ed324d'select * from tableT where varcharColumn=@vquery runs for a 2-3 seconds.Why this to queries has a huge performance difference? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-07-07 : 08:18:30
|
| What does the execution plan show you?Mike"oh, that monkey is going to pay" |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-07 : 08:26:20
|
Maybe varchar vs. nvarchar?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
janis
Starting Member
7 Posts |
Posted - 2009-07-07 : 08:57:02
|
quote:
Maybe varchar vs. nvarchar?
No, table column is varchar(125), same as declared variable. quote:
What does the execution plan show you?
In first case(when value is hardcoded) execution plan says 'index seek: scan a particular range of rows from nonclustered index', so that is okay and performance is good.In second case(when variable is used) execution plan says 'cluster index scan: scanning a clustered index, entirely or only a range'.Hm, why he is using different indexes in these cases? |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-07-07 : 09:48:53
|
| Ok, that's interesting. Next question. What column(s) are in the index seek index and what column(s)are in the clustered index scan index?Mike"oh, that monkey is going to pay" |
|
|
|
janis
Starting Member
7 Posts |
Posted - 2009-07-07 : 10:33:11
|
| Index seek: column 'varcharColumn'.Clustered Index scan: 'rowID', this is a primary key.One thing is also strange, sql server(in this execution plan) says:"The Query Processor estimates that implementing the following index could improve the query cost by 96.3237%."That index is nonclustered index on all other columns in the table except 'varcharColumn'?!? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-07 : 10:46:01
|
When you hardwire a WHERE clause, the execution plan is made exactly for that value.When you use a variable, the execution plan is made to accomodate all possible values for the variable. Doing this, the execution plan is mostly not optimal.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
janis
Starting Member
7 Posts |
Posted - 2009-07-07 : 10:55:14
|
| Execution plan maybe isn't suitable, but query results are my main concern. How that sql engine doesn't internally implement these two queries into same one?!?Problem can be solved by using dynamic sql query. Dynamic sql query "execute sp_executesql 'select * from tableT where varcharColumn=''' + @v + ''' " is working just fine. But, if I want to use this query in UDF then I have a problem again. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-07-07 : 11:16:37
|
| Try using the forceseek option on your variable query, and see if that bumps it to the index seek.for example:SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 OR T.b = 2;Mike"oh, that monkey is going to pay" |
|
|
|
janis
Starting Member
7 Posts |
Posted - 2009-07-08 : 03:42:18
|
| This doesn't help, I got ""FORCESEEK" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90." |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-08 : 03:47:20
|
FORCESEEK is available in sql server 2008.Fred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
janis
Starting Member
7 Posts |
Posted - 2009-07-08 : 06:37:23
|
| This problem I have on 2005. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-07-08 : 14:02:45
|
| woops...sorry about that.Mike"oh, that monkey is going to pay" |
|
|
|
janis
Starting Member
7 Posts |
Posted - 2009-07-09 : 02:21:39
|
| Any other idea or explanation? |
|
|
|
|
Index on varchar field problem?