Re: performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 10:35:39 +0100

On Thu, 13 Jan 2005 07:13:12 -0800, Ed wrote:

>hi all,
> I am trying to tune up the performance of some of the sql statements.
> I am under the impression if i can avoid the table scan and clustered
>index scan, that should be about 80% good, am i right?
>Thanks
>Ed

Hi Ed,

There are no such simple rules, unfortunattely. Table scan and clustered
index scan might often be bad, but sometimes the alterative is even worse.

use pubs
set statistics io on
set statistics time on
select * from titles with (index(UPKCL_titleidind))
select * from titles with (index(titleind))

The first query uses a clustered index scan. The second query uses a
(nonclustered) index scan and a bookmark lookup. The number of logical
reads goes from 2 to 37 - an increase of 1750%. The execution time goes
from 0 ms to 8 ms (on second execution of the script - the first execution
doesn't provide a fair comparison, as the data has to be read in cache
first).

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
Loading