Slow queries

[Shane]

Howdy. I have a table in my DB that has about 2 million records. The search times are taking 15 - 30 seconds depending on the number of records I am returning. Is this normal? The machine is NT 4 sp6a Dual PIII 866's with 1 GB of RAM on RAID5 SCSI disk. This seems like a long time to me. What kind of performance should I expect? Any kind of tuning steps I can take?

Thanks


Shane

[rmiao]

Use show plan to see if any index used.

[Shane]

clustered index scan

primeind for field1. My query is:

select * from table where field4 = 'NAME'

Can I have another index for field2 and field4? These are actually the fields that are indexed the most.

[rmiao]

Clustered index scan is same as table scan. Do you have non-clustered index on field4? If so, use index hint in the query.

[Shane]

K. I think I figured it out all last night. The table was indexed by field 1. The appliaction primarly does lookup by field2 sometimes field4. So I changed the primary index to field2. It is a clustered index. It has a fill of 90 and it using the PRIMARY file group. Does this sound correct?

Can I create a secondary index for field4? If I do this what should the setting be?

Thanks a million for your help.


Shane

[rmiao]

Yes, you can create non-clustered index on field4.

[WizKid]

Originally posted by rmiao
Clustered index scan is same as table scan. Do you have non-clustered index on field4? If so, use index hint in the query.

RMiao, I am little intrigued by your statement "Clustered Index is same as table scan". Can you pls elaborate.
My understanding of a clustered index in that the leaf page becomes the data page. How ever, an clustered index scan is not searching the leaf/data pages (table) but is scanning the (fewer) root and non-leaf pages; which would be much more faster that reading the entire TABLE.

Correct me if I am wrong.

Appreciate any info..

[rmiao]

Sql uses index in two ways. An index seek means sql uses index in a virtical manner, starting from the root and traversing through the levels of the index to find the data it needed. An index scan means sql uses index in a horizontal manner, scanning all or part of the leaf level of the index. Refer to Kalen Delaney's article 'Time for a Tune-Up' in sql server magazine.

[WizKid]

Wow!! That never stuck me!
learn something every day... ;-)


Thank you !!!