Bulky Database Tables

I am working on a program that could get the whereabouts of an item from the database. Problem is it is querying from a SQL table of 9 million records. Appreciate if some one could advise me the fastest/effective way to get the records.

 I tried to use sql stored procedure to insert into the temp table and then grab all the records from the temp table. But it took about 4 mins, which is not really acceptable.

I am using asp.net 2.0 connecting to sql server 2000.

indices?

There are indexes. I tried another method to reduce the records involved. Still I would be interested if anyone could suggest any method to retrieve bulky records.

but you're only retrieving one row...?

Your query should be optimised to make full use of the indexes. Explain execution plans should be useful. I never encourage the use of temp tables if they can be avoided. Take a look at this link: http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx. Of course, there are exceptions to it, especially when it comes to huge databases. So, check this link http://www.quest-pipelines.com/newsletter-v3/0602_D.htm out and see if the tips are useful.

cruizer:

but you're only retrieving one row...?

 It varies. Depending on the user's selection.

 

If the queries you are executing are based on column factors not being indexed, they do not take advantage of the indexes, and thus offer no benefit. Tuning indexes involves recognising how the table(s) get queried and looked up most of the time and which columns are used to determine what rows to return. Are the indexes indeed being put to good use? Take a look at the query execution plan for your stored procedures for estimates on to the cost of each query.

Triggers is also something that I would advise to avoid. Try using transactions instead. Sometimes it is better to update a total field of the master table each time a detail record is inserted or updated. That said, it is better to test the performance since you have a large database.

Also, although indexes help, do ensure that your indexes are well maintained. Indexes on foreign keys can also be created, or tables can be de-normalised if necessary, for performance.

one should also evaluate the relative uniqueness of the values stored in an indexed column. for example if the field is a gender field with male/female values, it's not worth indexing. but if it's a first name column the values are likely to be more or less unique compared to each other so an index is useful in this case.