Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnyquan
Starting Member
2 Posts |
 Posted - 2009-04-23 : 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'? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-23 : 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 plan2)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) above7)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 - 2009-04-23 : 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'? |
|
|
|
|
|
|
|
|
A strange problem with 'select' performance