The database looks like this:


Parameters has 126000 rows, which link an ExperimentID to the starting parameters for that ExperimentID. Simulations has ~107M rows, but I really only query against Xa (a floating point value with mostly unique entries). I'm interested in the other data, but for now it's just there. The current indices I have are the Primary Key index on Simulations, which is clustered, and a nonclustered columnstore index on the following columns:


Here's an example query I might be running against this database:

USE IMTsimulations
SELECT DISTINCT p.ControlRange, p.SensorError,
    PERCENTILE_DISC(0) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_MIN,
    PERCENTILE_DISC(.25) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Quartile25,
    PERCENTILE_DISC(.5) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Median,
    PERCENTILE_DISC(.75) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Quartile75,
    PERCENTILE_DISC(1) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_MAX,
    AVG(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Mean,
    STDEV(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_STDEV,
    COUNT(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as TotalEvents

FROM dbo.Simulations s JOIN Parameters p ON s.ExperimentID = p.ExperimentID
WHERE s.TimeElapsed <= 7200 and p.ExogDexCurve =  4
ORDER BY p.ControlRange, p.SensorError

As you can see, I'll be mostly looking at aggregates of Xa according to certain starting parameters. I feel like my primary key shouldn't be cluster, and the columnstore index should be clustered, and it should only have Xa, but I don't know for sure. The dataset is complete, so there will be no INSERTs, UPDATEs, DELETEs, or anything like that on either table.

What indexes should I be building for this thing? What's going to let me efficiently query aggregates on Xa that are partitioned according to things in the Parameters table?

I'd appreciate any depth you go into, as I'm doing this on my own, and don't have much technical background with databases.

  • How significantly does the s.TimeElapsed <= 7200 limit the number of aggregated rows? – Vladimir Baranov Nov 11 '15 at 2:34
  • TimeElapsed ranges from 0 to 7205, in increments of 5. I'm only interested in stuff that happens from 0 to 7200 for the most part. I could just delete the ones with 7205, but I see no reason when there's only about 50,000 rows that have TE = 7205. Should I use a different type of index for it, since its values are all set in stone? The same goes for everything except up to TertiaryCon, everything after that is a float with a very broad spectrum of possible values. Also, when should I have more than one thing in an index? – ijustlovemath Nov 11 '15 at 2:40

You said, that TimeElapsed is not really filtering much, so there is no point in indexing it.

Just looking at the query structure, I'd add an index to Parameters table on (ExogDexCurve, ControlRange, SensorError). The order of columns is important.

I would also make sure that all indexes have Fill Factor=100, because the data is static.

But, overall, the main bottleneck is reading all Xa values for the needed ControlRange and SensorError.

The server has to read all this data. Storing it in columnstore index should help.

I would also consider denormalizing the data and making just one table (persisting the join). In this case I'd create a covering index. The order of columns is important.

(ExogDexCurve, ControlRange, SensorError, Xa) INCLUDE (TimeElapsed)

Xa is part of an index rather than included, because you order by Xa.

With one joined table and this covering index the query should scan the index without extra sorts.

Also, to "hide" the persisted joined table it may be possible to create an indexed view of the join instead of explicit table, but I'm not sure.

| improve this answer | |
  • How about those Partition By parts? My queries are usually partitioned/grouped according to a unique combination of ControlRange and SensorError. Also, which index should be clustered? Does it matter for query speed? – ijustlovemath Nov 11 '15 at 3:18
  • @ijustlovemath, in the final covering index: ExogDexCurve is the first column in the index, because you filter using equality comparison by it. Then goes ControlRange, SensorError, because you partition by them. Then goes Xa, because you order by it. Then TimeElapsed is included. Such index is sorted as needed for the query and the query would read only index, so other indexes (clustered or not) don't matter much. – Vladimir Baranov Nov 11 '15 at 3:29
  • Ahh that makes sense. Since the DB is read only, am I limited to some number of indices, or can I build one for each group of similar queries? – ijustlovemath Nov 11 '15 at 3:31
  • Maximum Capacity Specifications for SQL Server 999 Nonclustered indexes per table. In practice you are limited by the disk space. Also, you should try to make indexes that could be used in more than one query, because once the data is read from disk into memory for one query you'd want to reuse it for other query, if possible. – Vladimir Baranov Nov 11 '15 at 3:40
  • Got it. I think I'm going to use a view and index that to get around the join thing and avoid data duplication. Are there any drawbacks to using the view vs creating a copy with the denormalized data? – ijustlovemath Nov 11 '15 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.