Re: Optimising queries

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, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: sqlParameter and NULL
    ... SqlCommand cmd = new SqlCommand("SELECT idx FROM foo WHERE ... SqlParameter para = new SqlParameter; ... strings to the query (e.g. string.concat("SELECT * FROM foo WHERE col ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Optimising queries
    ... optimising a query with regard to indexes. ... it matters what index on 'foo' that you have. ... thinks it much use to include both in the clustered index, ... pick one and make it (foo, barney) or. ...
    (comp.databases.ms-sqlserver)
  • Re: sqlParameter and NULL
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... I've got problems using a query like 'SELECT idx FROM foo WHERE ... this query and execute the command everything works fine. ... Is there another solution for querying NULL values via SqlParameter ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Displaying date through sql query?
    ... simple query: ... with foo ... union all ... i am working on SQl server. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)