Optimising queries

Hi.

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.

Cheers

Chris WestonChris 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".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"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|||Chris Weston (chrisweston[losethislot]@.ntlworld.com) writes:
> 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?

There is no such thing as free lunch, and an index comes with a cost yes.
The more indexes there are on a table, the longer inserts, updates and
deletes will take. It's difficult to quantify. Adding one more index, rarely
gives dramatic effect on these operations, but eventually there may be a
straw that breaks the camel's back. On the other hand, adding an index
can have drastic impact on a query, usually to the better.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||for help looking at creating indexes, look at the where clauses in your
select statements.

Find things that are pretty unique, or at least define what you want
down pretty closely.

Dates are usually good. Male/Female or Yes/No is really bad.

Don't be shy about using composite indexes, but the order of things in
your composite index can be important.|||oh.
the overhead for indexes comes in teh creation (one time, not too bad),
inserts, and deletes and updates.

Not usually a HUGE issue unless you are doing huge transaction tables.