October 13, 2003 at 9:15 pm
help! SQL optimizer is deciding not to use indexes because of index density (too many items with the same value), resulting in table scans. I have experimented with clustered indexes on the most searched fields, with great success. The problem is there is more than one commonly searched field. We are talking about a database that has 1-2 million records added per day of financial transaction data (very repetitive) and a need to retain 7 yrs of data. Table scans are totally unacceptable.
October 14, 2003 at 12:58 am
Hi,
your DB is much bigger than the one we are working with - we have a maximum of about 20 million records in a table - so I'm not sure whether I can really help... Did you try to implement WITH(INDEX(index_name)) in your queries? It helped me quite a lot in such cases where the optimizer decided not to use indexes, cutting the time of some complex queries down from over 10 minutes to 20 seconds or less. You can specify an index for each table in a query this way.
If you are looking for some way to tell the optimizer to use indexes generally, I'm sorry, but I have no idea how to do that, or whether it can be done at all.
October 14, 2003 at 1:20 am
quote:
help! SQL optimizer is deciding not to use indexes because of index density (too many items with the same value), resulting in table scans. I have experimented with clustered indexes on the most searched fields, with great success. The problem is there is more than one commonly searched field. Table scans are totally unacceptable.
If you had only non-clustered indexes so far it is no suprise that the optimizer chooses table scans over using the non-clustered index, because as you write your column values do not disperse that much.
What is your major concern?
I guess, once the rows are inserted they do not change at all, right?
Well if so, and you are concerned that it takes too long to retrieve the rows, have you considered using the FAST (n) query hint?
Another thought!
When you have to keep the rows for 7 years, do you need to keep them in one table? I mean, have you considered splitting into an 'actual' and 'archive' tables?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 14, 2003 at 2:03 am
Also, you'll probably find that the statistics are out of date.
Try running the query and profiling with the Profiler for warning and statistics events, or run the same query from query analyzer with "Show execution plan" on, this will warn you of any missing statistics.
Then create statistics for each of the necessary columns and keep them up to date - or turn on auto create and auto update statistics.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply