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 |
|
pvsramu
Starting Member
17 Posts |
 Posted - 2007-01-14 : 22:20:31
|
| Hi,The below query is running slow in production. SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ???CALL_TB table has three indexes. (1) primary key on CALL_ID (2) non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on PHONE_ID (IX_CALL_TB)The execution plan shows:SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171 |--Stream Aggregate(DEFINE:([Expr1002]=MIN([CALL_TB].[STMT_DATE]))) |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([CALLDB].[dbo].[CALL_TB]) WITH PREFETCH) |--Index Seek(OBJECT:([CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]), SEEK:([CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark Lookup takes about 9.82. Overall select statement takes estimated cost about 9.83To reduce the estimated cost, I have modifed the non-clustered index on PHONE_ID to include the column STMT_DATE. In order words, I made it as composite index. First column in the index is PHONE_ID and second column is STMT_DATE.Since I made it as covering index, the query estimated cost took about 0.008, which is a significant improvement. I have a question here....Does this change affects any other query to run slow? I mean, for example if there is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a, CALL_DETAIL b WHERE a.PHONE_ID = ??? and b.PHONE_ID will get affected?Thanks,Ramu |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-15 : 04:37:15
|
| You have made the index covering for the query (note in v2000 you can include the column rather than adding it to the index).The clustered index columns are included in the non-clustered index so anything that uses Phone_id will still be able to use the index.It may still cause problems due to mistakes by the optimiser but you can't tell without testing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
index issue