By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Optimising queries

P: n/a
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
Mar 23 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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".

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 23 '06 #2

P: n/a

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@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
Mar 24 '06 #3

P: n/a
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 24 '06 #4

P: n/a
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.

Mar 24 '06 #5

P: n/a
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.

Mar 24 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.