1

Is it a good idea to include a value column into an index for a table which has many million rows?

For example a measurement table which looks kind of like this:

CREATE TABLE measurements
(
    id bigint IDENTITY,
    parameter_id int NOT NULL,
    measuretime datetime NOT NULL,
    value float NOT NULL,
    PRIMARY KEY CLUSTERED (id ASC)
);

ALTER TABLE measurements ADD FOREIGN KEY (parameter_id)
REFERENCES parameters(id)

When I run a simple avg query the execution plan suggest to create an index which also includes the value column.

SELECT AVG(value)
FROM measurements
WHERE measuretime BETWEEN '2015-01-01' AND '2015-02-01'
GROUP BY parameter_id

execution plan

CREATE NONCLUSTERED INDEX [idx_measurements_mt_pi_v]
ON [dbo].[measurements] ([measuretime])
INCLUDE ([parameter_id],[value])

I get that it's a good idea to have an index on measuretime and parameter_id, but isn't storing every value in an index a bit over the top?

Won't this result in a massive index? As values are probably always unique because of their precision. Or is just my concept of what include does wrong?

Besides is this really necessary, as the query is running only for about 2-3 seconds with 38.000.000 row (but I guess this could have a much higher impact on a long running production database)?

I'm also thankful for any other performance tips regarding this kind of data query!

EDIT: As suggested I've added the index and ran the query again with SET STATISTICS IO ON and SET STATISTICS TIME ON again

Output:

SQL Server parse and compile time: 
   CPU time = 15 ms, elapsed time = 17 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 9 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(12 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'measurements'. Scan count 5, logical reads 24212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1391 ms,  elapsed time = 481 ms.

And here is the new execution plan:

enter image description here

  • I am sure NCI would benefit here. See parallelism coming into picture and this is what making the query fast IMO. If you create NCI there wont be parallelism in picture, can you create the NCI and post the execution plan after that. Plus I wont worry so much about size if it really decreases query execution time and helps in running query faster – Shanky Mar 23 '15 at 10:50
  • I've edited my question accordingly ;) – Staeff Mar 23 '15 at 11:23
  • Irrelevant to the efficiency and index issue but please check this: What do BETWEEN and the devil have in common? Whatever that BETWEEN of yours is supposed to do (I guess it is to filer only 1 month), it's not entirely accurate. Some rows from February may slip in. – ypercubeᵀᴹ Mar 23 '15 at 12:22
3

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time:

CREATE TABLE measurements
(
    id bigint IDENTITY,
    parameter_id int NOT NULL,
    measuretime datetime NOT NULL,
    value float NOT NULL,
    PRIMARY KEY NONCLUSTERED (id ASC)
);

CREATE CLUSTERED INDEX cdx_measurements ON measurements(measuretime);

Yes, lookup by id will take two reads instead of one. Who cares, id is looked up in singletons. Is a trade off.

  • I was thinking about this alternative before, and now after trying it out this really boosted things up! It doesn't really answer my question if it's a good idea in general to add measurement values to an index, but for my case this is probably still the best option, thanks! – Staeff Mar 23 '15 at 12:39
  • @staeff: I recommend you read this series: sqlskills.com/blogs/kimberly/category/the-tipping-point – Remus Rusanu Mar 23 '15 at 13:00
  • 1
    Is also important to evaluate, if you have, the access path that requests specific parameter_id values. Eg. SELECT AVG(value) FROM measurements WHERE measuretime BETWEEN '2015-01-01' AND '2015-02-01' AND paraemeter_id=42. Such a query require another index, something like ON measurements(parameter_id, measuretime) INCLUDE (value) but be aware that this nearly doubles your storage size as the clustered and non-clustered indexes are neirly the same size.. – Remus Rusanu Mar 23 '15 at 13:07
1

I think the optimiser is right. When you use INCLUDE, it only stores the included column values on the leaf level of the index, they do not make up the key. So what it is suggesting is that it can decide which branches of the index to scan (measuretime is the key, so it leaves a huge chunk of records out), which means the WHERE doesn't need to test each row. Having left out most of the table it then just adds up the values on the leaf level and divides by the number of records according to the grouping.

Essentially without the index the WHERE has to scan every row in the table to test whether its got the right date or not. The clustered index scan shown isn't that efficient compared to knocking out a huge chunk of the rows using an index.

Have you tried adding the suggested index and checking the performance using?

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
  • I've edited my question with the index and statistics. – Staeff Mar 23 '15 at 11:23

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.