June 23, 2015 at 10:04 am
Hi,
I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
Thanks,
Pedro
June 23, 2015 at 10:22 am
If you can arrange the columns in the order most-searched to least-searched, start with indexes on the most-searched and add others as conditions dictate. Also, consider combinations. e.g. if columns 3 and 4 are always searched in that order, than an index on (col3, col4) may be warranted.
Bottom line: you may not want to index everything since the cost of index maintenance may outweigh the advantages, especially for lesser-used columns. You'll need to experiment to strike the right balance.
BTW, what is the datatype of the columns? e.g. if all text, could you concatenate them and use full-text search instead? Just a thought.
Gerald Britton, Pluralsight courses
June 23, 2015 at 10:24 am
PiMané (6/23/2015)
Hi,I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
Thanks,
Pedro
Quick suggestion, look at Gail Shaw's Catch-all queries[/url] and Erland Sommarskog's The Curse and Blessings of Dynamic SQL
😎
June 23, 2015 at 10:25 am
You may find this [/url]related article interesting.
You're certainly going to have to do some indexing, but the optimal set of indexes will depend on the search patterns. Get some users to tell you their typical searches and then plug those in to the query, tracking the associated execution plans. Then build some indexes around that.
When the functionality is live, you (or your DBA) should continue to track usage/search patterns and identify possible missing indexes.
June 23, 2015 at 10:28 am
The table is only updated on scheduled tasks, so the "cost" for insert / update is low or none.
I already read Gail's article and I'm using dynamic SQL to build the query.
This is a portal database with accesses from every corner of the word with users that can search for anything or nothing in specific so it's very difficult to get the right columns.
I could monitor using the missing indexes DMVs but so far it returns over 30 indexes with an average of 7 columns each... few large indexes vs lots short indexes...
June 24, 2015 at 12:40 am
First, you have to decide what to measure so you know how good your DB performs. Actually the best indexes today may not be the best next year if search patterns change.
You may measure CPU, duration etc. for you search queries. Also you may want to monitor best, average or worst queries. Some times it does not matter if 1000 users get results in 1 or 2 seconds but it does matter if 1 user does not get his result in 60 seconds.
So measure, change one thing at a time and measure again. This will be an ongoing process.
Ivan
June 29, 2015 at 10:33 am
Assuming you have a large table which is batch loaded, essentially a flat data warehousing type table (not OLTP), and there is a need to perform ad-hoc type queries against any combination of several columns, then perhaps consider Clustered ColumnStore. When compared to a traditional RowStore table with separate indexes on each searchable column, there is a good chance that ColumnStore will prove to consume a fraction of the disk storage, query faster, and load faster. Even if the queries are single row bookmark type lookups (rather than aggregate queries), the ColumnStore may be the way to go for the reasons stated above.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 29, 2015 at 10:41 am
Thanks,
I'll try it.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply