How to speed up search using multiple indexes for same table

[jadoogar]

In Foxpro etc we had multiple indexes and filtered indexes
for tables and to speed up search we could have used
set index to 1 , 2 etc to specify which index

is there such an equivalent way in SQL data if so using
VB how can we specify which index may be used?
any idea

I have a table with 7 million records and searching
takes time where as I can reduce time if it were in foxpro
using different indexs say index 1 for records where key_id
between 1 and 100000
and index 2 if key_id between 100001 and 200000
etc
Catch my Point?

 

[gmmastros]

Have you tried using the Index Tuning Wizard that is part of the Query Analyzer. It does a decent job of determining the index(es) that could be created to speed up execution.

Load your query in to a new window, then click Query -> Index Tuning Wizard.

After it is complete, and you've implemented the index suggestions, run the query again. If there is little or no improvement, then you should look at the execution plan. Press CTRL-K, then run the query again. At the bottom of the window, there will be an Execution Plan tab. Look for table scans (the worst) and then look for index scans (better but still not good enough). Ideally, you'd like to see the query using Index Seeks's.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

If you're talking about using indexes to search in a Select query, you're in luck. Assuming you're using T-SQL to do the search.

Hints can often speed up T-SQL queries if used correctly. You can look up details about this in Books Online.

Here's the syntax:

Select * 
from MyTable with (Index(My_Index_Name))
Where <insert conditional>

The "with (Index(My_Index_Name))" is the hint part of the syntax. Also look up "NoLock" in BOL for locking hints which can speed up query time.

You can find the name of your indexes (if you don't know them), by going into the table design in Enterprise Manager and clicking the second icon to the left. Then hit the Index tab and click the drop down list to display the index names. Each one you choose should tell you what columns and rows you have as part of the index.

Hope this helps!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???