August 6, 2008 at 1:42 pm
Given a web search page where a user can select up to 10 different criteria and therefore having the need to create a dynamic where clause to handle the search condition, what is the best technique to index the table on the back end given that over 20 million records will be searched?
I want to prevent a full table scan at all costs as the results will never return in time. However, if I was to create 10 single column indexes, I would think I would run into performance issues as well when a user picks more than one selection.
Any thoughts would be appreciated.
Thanks
August 6, 2008 at 2:28 pm
Without knowing the specifics of your database design, lets make some "dangerous" design assumptions. Let me know if I'm off in left field here.
1. The table being queried is a single table.
2. The performance question stems from the fact that you'll have a high number of insert, update, and delete operations against the table being queried.
3. You can't control the database design. In other words, you can't break up this table into several smaller tables and instead of having the query's WHERE clause be dynamically generated, you can't include or exclude additional tables in the dynamicly built query in the FROM clause using JOIN operations.
4. When you say "up to", I assume that at a given time the where clause can have less than 10 conditionals.
Now if you can break this table up into several smaller tables, you'll generally improve the performance of non-read operations. The reads will run from fastest to slowest depending on the total number of joined tables in the generated query. However being a realist, we all know that we come across situations where we simply cannot change the physical table layout.
Keep in mind that additional indexes only cause performance issues generally with writes. Read performance is generally enhanced. For example, in large data warehouses with flattened table design, its not uncommon to see many more indexes on a single table than you would see with an OLTP application database that's fairly normallized.
Give me some idea of the write-based operations against this table. Are they more "batch" oriented, happening relatively few times per day or are they more "transactional" in scope, happening randomly throughout the day?
August 7, 2008 at 4:14 am
Without seeing any more info on this, it's hard to make a blanket statement. Generally what I would do is index for the most frequent queries. If you don't know what that will be, guess and then once the app is being used go back and revise the indexes based on the actual usage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply