Microsoft SQL Server forum - Optimising queries

Author

Optimising queries

Chris Weston

2006-03-23, 1:31 pm

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 Weston

Chris Weston

2006-03-24, 3:26 am

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns978FEFA60586
BYazorman@127.0.0.1...
> Chris Weston (chrisweston& #91;losethislot]@ntl
world.com) writes:
such[color=darkred]
how[color=darkred]
>
> 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

Erland Sommarskog

2006-03-24, 3:26 am

Chris Weston (chrisweston& #91;losethislot]@ntl
world.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

Doug

2006-03-24, 11:34 am

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.

Doug

2006-03-24, 11:34 am

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.

Sponsored Links