1

I am working on a new search function in our software where a user will be allowed to search on any or all of 3 possible fields A, B and C. I expect that if anything is entered for a field it will be a complete entry and not just a partial one.

So the user choices are

  • Field A, or
  • Field B, or
  • Field C, or
  • Fields A & B, or
  • Fields A & C, or
  • Fields B & C, or
  • Fields A, B & C.

My question is what indexes should be created on this table to provide maximum performance? This will be running on SQL Server 2005 and up I expect and a good user experience is essential.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Robert C
  • 53
  • 3
  • 1
    Is this table select heavy and insert/update/delete light? – UnhandledExcepSean Aug 13 '14 at 17:36
  • All 3 fields are non-null integers. There will be virtually no updates or deletes. There might be 100s of selects for every insert over the life of it. Thank you! – Robert C Aug 14 '14 at 20:29
  • Also, the creation of the sql query will indeed be dynamic once I know which of A, B and/or C I have in hand at that instant. – Robert C Aug 14 '14 at 20:31

3 Answers3

0

Assuming searches are much more numerous, you will want to create an index on every subset of fields by which you wish to access your data. So that would be 6 indices if you wish to do it on the powerset of columns.

thebighouse
  • 61
  • 1
  • 2
0

I would recommend this basic approach.

1) Make sure your table has a clustered index which is Unique, Ascending, and Small (ideally an INT).

2) Create the following three non-clustered indexes:

CREATE NONCLUSTERED INDEX ON dbo.YourTable(a) INCLUDE (b,c, [plus any potential output columns])
CREATE NONCLUSTERED INDEX ON dbo.YourTable(b) INCLUDE (a,c, [plus any potential output columns])
CREATE NONCLUSTERED INDEX ON dbo.YourTable(c) INCLUDE (a,b, [plus any potential output columns])

3) Use the index DMVs to compare the times each index is hit. If an index is used heavily, experiment by adding two more indexes. (Assume the index with C as a single tree node is the heavily used index.)

CREATE NONCLUSTERED INDEX ON dbo.YourTable(c,a) INCLUDE (b, [plus any potential output columns])
CREATE NONCLUSTERED INDEX ON dbo.YourTable(c,b) INCLUDE (a, [plus any potential output columns])

Compare how frequently they're used verses the single tree node index. If they're not being used infavor of the single tree node, they may be superfluous.

In summary, start with a minimal covering indexes and experiment based on usage.

Jim V.
  • 2,137
  • 16
  • 14
  • Yes, the primary key is unique, ascending and an integer. I will give what you suggest a whirl once I get enough data built up. Thanks. – Robert C Aug 14 '14 at 20:39
0

this is difficult to answer without knowing your data or its usage. Hopefully A, B , and C are not long string data types. If you have minimal Insert/Update/Delete and/or will sacrifice everything for index usage, I would create an index on each of these:

A, B , C    <<<handles queries for: A, or A & B, or A, B & C
A, C        <<<handles queries for: A & C
B, C        <<<handles queries for: B, or B & C
C           <<<handles queries for: C

These should cover all combinations you have mentioned.

Also, you will also need to be careful to write a query that will actually use the index. If you have an OR in your WHERE you'll probably not use an index. In newer versions of SQL Server than you have you can use OPTION(RECOMPILE) to compile the query based on the runtime values of local variables and usually eliminate all OR and use an index. See:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

you can most likely use a dynamic query where you only add the necessary conditions onto the WHERE to get optional index usage:

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

You can also see this answer for more on dynamic search conditions

Community
  • 1
  • 1
KM.
  • 101,727
  • 34
  • 178
  • 212
  • Thanks! It occurs to me now that 'B' will naturally be the most selective field in that it will have the greatest variety of values. 'A' will be the next most selective and 'C' the least. In this case it might be better to make line 1 "B, A, C" and leave the others as they are. Hmmmm. – Robert C Aug 14 '14 at 20:36