| Author |
 Topic  |
|
|
johnyquan
Starting Member
2 Posts |
 Posted - 04/23/2009 : 03:29:09
|
My statement is as followed:
I have a table with 2 million records and do a query with the SQL
"select * from TBL where fld_1=@value"
(fld_1 is the primary key and defined as char(8)).
1)when @value is N'10000009', it takes about 0.5 second.
2)when @value is '10000009',it takes about 0.1 second.
3)when @value is string which ends with 9 and length is 8,it takes about 0.5 second.
4)when @value is sting which ends with 9 and length is lessthan 8,such as N'10000008',it takes about 0.1 second.
5)when @value is string which does not end with 9,it takes 0.1 second.
6)when fld_1 is defined as nchar(8) and @value is N'10000009',it takes about 0.1 second.
7)when all of the indexes(the default primary index also included) are droped,it takes the same time whatever the @value is.
In my project a complexible SQL is used and the difference described above is expanded about 30 times.
Does anyone know the special '9'?
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 04/23/2009 : 03:36:06
|
1)when @value is N'10000009', it takes about 0.5 second.
Converting between unicode and non-unicode makes the time difference. See the execution plan
2)when @value is '10000009',it takes about 0.1 second.
Yes, now column and variable has same datatype.
3)when @value is string which ends with 9 and length is 8,it takes about 0.5 second.
Unicode or non-unicode?
4)when @value is sting which ends with 9 and length is lessthan 8,such as N'10000008',it takes about 0.1 second.
Is execution cached?
5)when @value is string which does not end with 9,it takes 0.1 second.
No matter what length?
6)when fld_1 is defined as nchar(8) and @value is N'10000009',it takes about 0.1 second.
See 2) above
7)when all of the indexes(the default primary index also included) are droped,it takes the same time whatever the @value is.
Probably because now a table scan occurs. See execution plan.
E 12°55'05.63"
N 56°04'39.26" |
 |
|
|
johnyquan
Starting Member
2 Posts |
Posted - 04/23/2009 : 03:58:40
|
Thanks for your reply.
1)when @value is N'10000009', it takes about 0.5 second.
Converting between unicode and non-unicode makes the time difference. See the execution plan
=>but why the statement 5) happened?
3)when @value is string which ends with 9 and length is 8,it takes about 0.5 second.
Unicode or non-unicode?
=>unicode values like N'10000019', N'10000129' and etc.
4)when @value is string which ends with 9 and length is less than 8,such as N'10000008',it takes about 0.1 second.
Is execution cached?
=>I don’t think so. The statement 1) takes 0.5 second every time.
5)when @value is string which does not end with 9,it takes 0.1 second.
No matter what length?
=>yes. values just like N'10000001', N'10000002', N'100011'.
can you help with the '9'?
|
|
|
| |
Topic |
|
|
|
A strange problem with 'select' performance
