|
|
Posted by Chris Weston on 03/24/06 09:01
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns978FEFA60586BYazorman@127.0.0.1...
> Chris Weston (chrisweston[losethislot]@ntlworld.com) writes:
> > Maybe I'm just being dim, but I'm struggling to get my head around
> > optimising a query with regard to indexes. If I make a select query,
such
> > as a pseudo-example 'select * from bigtable where foo='bar' and
> > (barney>rubble and fred<flintoff)', and the table is indexed on 'foo',
how
> > could I make that any better? What indexes could I add, or what could I
> > change in the query?
> >
> > I know it looks simple, but so am I.
>
> First of all, it matters what index on 'foo' that you have. Is that a
> clustered index or a non-clustered index? For this query a clustered
> index is is likely to be better, but since you only can have one clustered
> index on a table, there may be better choices for other queries.
>
> It's unclear to me what
>
> (barney>rubble and fred<flintoff)
>
> is supposed to mean, but I assume that barney and fred are columns and
> 'rubble' and 'flintoff' are values.
>
> It's difficult to cover this condition well in a single index. I don't
> thinks it much use to include both in the clustered index, but you should
> pick one and make it (foo, barney) or (foo, fred).
>
> If you have to use non-clustered indexes is a little different.
> (foo, barney, fred) is proabbly more effective than (foo, barney),
> because SQL Server does have to access the data pages to check
> the condition on fred.
>
> Yet an idea, is to have (foo, barney) and (foo, fred) and see if
> SQL Server may use index intersection.
>
> As for changing the query, that's difficult, because I don't know what
> it is supposed to mean.
>
> Overall, it's difficult to give generic advice for performance issues,
> since there are a lot of "it depends".
>
I should have been clearer over the conditions, but you made the correct
assumption. That's useful advice, thank you very much. I can certainly add
more indexing as you suggest, but is there any performance or resource
overhead in having many indexes?
Thanks
Chris Weston
Navigation:
[Reply to this message]
|