Index structure for maximum speed in any combination of index columns

Question

I have a database with five possible index columns, all of which are useful in different ways. Let them be called System, Source, Heat, Time, and Row. Using System and Row together, you will create a unique key and, if sorted by System-Row, the database will also be sorted for any combination of the five index variables (in the order in which they are listed above).

My problem is that I use all combinations of these columns: sometimes I want to JOIN each System row to the next System (Row + 1), sometimes I want GROUP or WHERE through System-Source-Heat, sometimes I want to see all System records -Source WHERE. Time is in a specific window, etc.

Basically, I want the index structure to function similarly to every possible permutation of these five indexes (in the correct order, of course), without actually doing every permutation (although I am ready to do this if necessary). I am engaged in statistics / analytics, and not traditional work with databases, so the size of the index and the speed of its creation / updating are not a problem; I only care about speeding up my impromptu requests, as I tend to invent them, launch them, wait 5-10 minutes, and then never use them again. Thus, my main concern is to “wait 5-10 minutes” for something more, for example, “wait 1-2 minutes”.

My sorted data looks something like this:

Sys So H Ti R 1 1 0 .1 1 1 1 1 .2 2 1 1 1 .3 3 1 1 2 .3 4 1 2 0 .5 5 1 2 0 .6 6 1 2 1 .8 7 1 2 2 .8 8 

EDIT: It can make it a little easier that the system should almost always be turned on as the first column to make any of the 4 columns in sorted order.

Answer 1

Sorry for taking the time to get back to this, I had to work on something else for several weeks. In any case, having tried a bunch of things (including everything proposed here, even the brute force method of "making an index for each permutation method"), I did not find any indexing method that significantly improves performance.

However, I found an alternative, non-indexing solution: selecting only the rows and columns that interest me in the intermediate tables, and then working with them instead of the full table (so I use about 5 mil rows of 6 cols instead of 30 mil rows of 35 cols). The initial creation of select and table is a bit slow, but the steps after that are much faster, I actually save time even if I run it only once (and considering how often I change things, this is usually many times).

I suspect that the reason for this huge improvement will be obvious to most SQL users (perhaps something like the size of the page file), and I apologize. My only excuse is that I'm a statistician trying to teach myself how to do this when I go, and although I'm pretty decent to get what I want to do (in the end), my understanding of the mechanics of how this happens made terribly close to "it's a magical black box, don't worry about it."

Answer 2

If you are ONLY connected with SELECT speed and don't care about INSERT, then you can materialize ALL combinations as INDEXED. You only need to store the original table 24 times, composing one table and 23 INDEXED VIEWs with 5 columns each.

eg.

 create table data ( id int identity primary key clustered, sys int, so int, h float, ti datetime, r int); GO create view dbo.data_v1 with schemabinding as select sys, so, h, ti, r from dbo.data; GO create unique clustered index cix_data_v1 on data_v1(sys, h, ti, r, so) GO create view dbo.data_v2 with schemabinding as select sys, so, h, ti, r from dbo.data; GO create unique clustered index cix_data_v2 on data_v2(sys, ti, r, so, h) GO -- and so on and so forth, keeping "sys" anchored at the front 

Note, however, Q. Why is my index view not matched by the query optimizer for use in the query plan? (search in related article)

---

If space is a problem, it is best to create separate indexes on each of the 4 columns leading to the system, i.e. (sys, ti), (sys, r), etc. They can be used together if the query helps, otherwise it will return to a full table scan.