| Author |
Topic |
|
ara3n
Starting Member
8 Posts |
 Posted - 2007-02-02 : 14:00:30
|
Hello I ran the following code in query analysizer on sql to study it. dbcc show_statistics ([Jason Pharm Test$Sales Shipment Header],[$1]) The table is Sales SHipment Header. The primary key is No_The secondary index in is Order No-, No- The density is .9997 There are 42696 records in the table. The following sql statement took 3130 ms. SELECT * FROM "testdb"."dbo"."TableName$Sales Header" WHERE (("Order No_" LIKE @P1)) ORDER BY "No_" OPTION (FAST 10) Compared to other sql statement is is taking 3 seconds. Which is high. I looked at execution plan and it uses clusterd key 82% compared to secondary key (Order No., No.). Why is sql doing this? Even if I change the Order by to "Order No_" it still uses clustered index. SELECT * FROM "testdb"."dbo"."Table$Sales Header" WHERE (("Order No_" LIKE 'SO7273')) ORDER BY "Order No_" OPTION (FAST 10) Why isn't sql using the secondary index? Should I change the secondary index to just Order No_ |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-02 : 14:23:55
|
What's wrong if SQL chooses to use Clustered Index? I think it's a good thing that it is using Clustered Index and SQL always tries to see if it can use CI first. Besides, 3 secs doesn't seem to be unacceptable time to me. Of course, it depends upon no. of records you have in the table. To me the only optimization you can have is replace LIKE with = operator (and of course, limit the no. of columns instead of *  )Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
ara3n
Starting Member
8 Posts |
Posted - 2007-02-02 : 14:37:48
|
| Why isn't SQL using the CL. It's the perfect index for this query. I can't change the sql statement. It's not possible. Another question. When you issue a SELECT SUM("s20") from tableA where blah = 'this'Does sql use the tempdb to do the calculation? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-02 : 14:46:11
|
quote:
SELECT SUM("s20") from tableA where blah = 'this'Does sql use the tempdb to do the calculation?
It depends on the complexity of operation and need to store intermediate results. I don't think SQL uses tempdb for a simple summing operation but it will definitely use tempdb complex joins over big tables.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
ara3n
Starting Member
8 Posts |
Posted - 2007-02-02 : 14:47:24
|
| The sql statement is run in serialized transaction. And this is just one of hundreds sql statements. There are 40 thousands records in that table. Everyday there are 1000 records inserted into this table in one year you'll have 350K records. If there are 10 users simultaneously running the transactions it will take all of them 30 seconds to complete. |
|
|
|
ara3n
Starting Member
8 Posts |
Posted - 2007-02-02 : 16:53:25
|
| I did some more testing and it looks like the statement doesn't always take 3 seconds. Sometimes it takes 200 ms. So My only guess is that under major load, that this is happening.It still doesn't answer the question, why SQL would not use the secondary index. If you are using clustered index, aren't you scanning the whole table? |
|
|
|
ara3n
Starting Member
8 Posts |
Posted - 2007-02-02 : 22:08:08
|
| I've gotten another answer. The tabel has 114 fields, and person told me that because there are too many fields in the table. sql decides that it is faster to to use clustered key. Is this true? I never heard of this. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-24 : 21:03:32
|
| It depends on query and rows involved, no matter how many columns the table has. By the way, nothing wrong to use clustered index. Sql doesn't scan table if plan shows clustered index seek. |
|
|
|
|
sql execution plan