Problems querying in large table

[impulse24]

Hi,

I have imported a text file into SQL Server 2000 Enterprise that contains 5,000,000 records. During the import I create a new table. The table contains 3 fields

Fieldname Datatype Length
Docid integer 10
SeqNo integer 3
Fname varchar 125

I have created a clustered index that contains all 3 fields. All 3 fields are set as unique. When I try to perform a query on any of the fields using an exact value I get an ODBC timeout error in Enterprise Manger. If I link the tables through Microsoft Access I can perform the query but it is terribly slow. I have run query analyzer, but it does not recommend any changes to be made. I have also looked in the online help and can't seem to find any other methods for optimizing. Is this too many records for SQL Server 2000 to handle? Can anyone help. Thanks

 

[JamesLean]

Well for starters it's certainly NOT too many records for SQL Server to handle!

If you've created a clustered index over all three fields - ie DocID, SeqNo, Fname - then this will only really help for queries which search on all three fields, or just DocID, or DocID and SeqNo.

If you're searching on just Fname for example, this index will not be able to be used properly and you may need to add another index on just that column.

--James

 

[billchris]

I seem to recall a recommendation from somewhere, but I don't recall where, that clustered indexes are best used when indexing a single field. If more than one field is used in the index, then a non-clustered index is better. I haven't verified this myself and don't know if it is true at all or even in only certain circumstances.

But, JamesLean is correct. I think you would want the clustered index on the field that is searched on the most often and a non-clustered index on each of the other fields that are searched.



“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal