index question

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.83
To 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
Adding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>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.83
>To 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
|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>The below query is running slow in production.
What do you mean, "slow"?

>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?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.