Prev: Tracking CAL on Server (MSSQL2005)
Next: ADO.NET from C++
From: Mo on 20 Jul 2010 17:53
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.
From: Plamen Ratchev on 20 Jul 2010 17:57
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
From: Erland Sommarskog on 21 Jul 2010 17:31
Mo (le_mo_mo(a)yahoo.com) writes:
> 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.

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Tracking CAL on Server (MSSQL2005)
Next: ADO.NET from C++