Hi,

I have a relatively large table which has around five million records.
When I run a query like:

Select A, B, C where A='AA' and B='BB'

the query takes a long time to complete which is slowing down the web
front end.

My question is how to go about indexing the table to make this query
faster? I have added two (Non Clustered and clustered) indexes to this
table but do not see any improvement in performance. Any
recommendations is greatly appreciated.

What indexes did you create? If this is a typical query you can add
covering index on the 3 columns (A, B, C). Are the column values
selective enough to use the indexes? Look at the execution plan to see
what is happening.

--
Plamen Ratchev
http://www.SQLStudio.com

Mo (le_mo_mo (AT) yahoo (DOT) com) writes:
For this particular query, the best index (assuming that the table has
more columns that these three) would be one of:

CREATE INDEX best_ix ON tbl(A, B) INCLUDE (C)
CREATE INDEX best_ix ON tbl(B, A) INCLUDE (C)

But if (A, B) are selective enough, an index on those two alone may be
sufficient. I'm a little wary of adding covering indexes for such queries,
since if someone later adds the column D to the result set, the index is
not covering anymore, just unecessarily large.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx