Clever Geek Handbook

SQL Server Indexes Don't Help

I have a table (SQL 2000) with over 10,000,000 records. Entries are added at a rate of about 80,000-100,000 per week. Several reports are generated from the data once a week. Reports are generally quite slow to run because there are multiple indexes (probably to speed up INSERT). One new report can actually benefit from an additional index on a specific char (3) column. "

I added the index using Enterprise Manager (Manage Indexes -> New -> select column, OK) and even rebuilt the indexes on the table, but the SELECT query didn't speed up at all. Any ideas?

Update

Table definition:

ID, int, PK
Source, char(3)  <--- column I want indexed
...
About 20 different varchar fields
...
CreatedDate, datetime
Status, tinyint
ExternalID, uniqueidentifier

My test query:

select top 10000 [field list] where Source = 'abc'
0


source to share


4 answers


You need to look at the query plan and see if it uses this new index - if not, then there are a couple of things. One - it could have used a caching plan that it uses that hasn't been invalidated since the new index was created. If it doesn't, you can also try the index hints [With (Index (yourindexname))].



10,000,000 lines is not unheard of, he has to read it pretty quickly.

+5


source


Use Show Execution Plan in SQL Query Analyzer to see if this index is in use.



You can also try making it a clustered index if it hasn't already been.

+1


source


For a table of this size, it is best to split the table and indexes.

0


source 


 select top 10000

How unique are your sources? Field indices that have very few values ​​are usually ignored by the SQL engine. They make queries slower. You can remove this index and see if it's faster if the SOURCE field only contains a few values.

0


source






More articles:


All Articles