improve query performance on SQL Server table contain 3.5 Million rows and growing

Question

I have written one application in C# which is connected to sql server database express edition, from front end I populate the particular table in database every few second and insert approx 200~300 Rows in this table.

Currently table contains approx 3.5 Million rows and its keep growing, the table definition is as below

[DEVICE_ID] [decimal](19, 5) NULL,
[METER_ID] [decimal](19, 5)  NULL,
[DATE_TIME] [decimal](19, 5)  NULL,
[COL1] [decimal](19, 5)  NULL,
[COL2] [decimal](19, 5)  NULL,
.
.
.
.
[COL25] [decimal](19, 5) NULL

I have created non clustered index on Date_Time column, and to note there is no unique column exists if it requires I can create identity column (Auto increment) to this but my report generation logic is totally based on Date_Time column.

I usually fire the query based on time, I.e. if I need to calculate the variation occurred in the col1 in the month period. I will need the value of Col1 on first value of 1st day and last value of last day of month, like wise i need to fire the query for flexible dates and I usually need only opening value and closing value based on Date_Time column for any chosen column.

To get first value of col1 for the first day, the query is

select top (1) COL1 from VALUEDATA where DeviceId=@DId and MeterId =@MId and Date_Time between @StartDateTime and @EndDateTime order by Date_Time

To get last value of col1 for the last day, the query is

select top (1) COL1 from VALUEDATA where DeviceId=@DId and MeterId =@MId and Date_Time between @StartDateTime and @EndDateTime order by Date_Time desc

But when I fire the above queries its takes approx 20~30 seconds, I believe this can be further optimized but don't know the way ahead.

One thought i given to this is to create another table and insert first and last row on every day basis and fetch data from this. But I will avoid the same if I can do something in existing table and query.

It’s greatly appreciable if someone can provide the inputs for the same.

Why all data types are `decimal(19,5)`? Especially for `Date_Time` this is bad idea. — i486, Sep 11 '16 at 19:35

Marc Guillot · Accepted Answer · 2016-09-03T13:13:25.640

1

To fully optimize those queries you need two different multiple indexes :

CREATE INDEX ix_valuedata_asc ON VALUEDATA (DeviceId, MeterId, Date_Time);
CREATE INDEX ix_valuedata_des ON VALUEDATA (DeviceId, MeterId, Date_Time DESC);

edited Sep 03 '16 at 13:13

answered Sep 03 '16 at 13:05

Marc Guillot

6,090
1
15
42

I would test first, but I suspect the second index would only provide minimal gain, if any. – ESG Sep 03 '16 at 13:19
First indexing make sense, let me test. – Real Master Sep 03 '16 at 14:16
Your second query would prefer the descending index. But yes, the gain would be quite small. – Marc Guillot Sep 03 '16 at 14:28
3

@MarcGuillot, only the first index is needed since SQL Server can perform an ordered backwards scan after the seek on the equality predicate. – Dan Guzman Sep 03 '16 at 14:44
Thanks, I didn't know SQL Server can go over an index backwards. – Marc Guillot Sep 03 '16 at 16:05
1

@MarcGuillot Thanks, I was testing it since last 2 days and Its has improved the performance of my queries considerably! – Real Master Sep 06 '16 at 14:20
I am not sure whether `Date_Time` must be last or first field in the index. It depends on the number of `DeviceId`s and `Date_Time`s. – i486 Sep 11 '16 at 19:37

score 1 · Answer 2 · edited May 23 '17 at 11:51

I have another suggestion: if your goal is to get the values of COL1, COL2 etc after you do the index lookup, the solution with just a nonclustered index on the filtering columns still has to join back to the main table, ie; do a bookmark / RID lookup.

Your info gives me the impression your base table does is not clustered (has no clustered index); is in fact a heap table

If most of your querys on the table follow the pattern you describe, I would make this table clustered. In contrary what most people think, you do not have to define an clustered index as the (unique) primary key. If you define a clustered index in SQL server on non unique data, SQL server will make it unique 'under water' by adding an invisible row identifier...

If the main, most often USED selection / filter criteria on this table is date time, I would change the table to the following clustered structure:

First, remove all non clustered indexes
Then add the following clustered index:

CREATE CLUSTERED INDEX clix_valuedata ON VALUEDATA (Date_Time, DeviceId, MeterId);

When using query's that follow your pattern, you (probably!) will get very performant Clustered index SEEK style access to your table if you look at the query explain plan.. You will now get all the other columns in the table for free, as bookmark lookups are not needed anymore. This approach will probably scale better too as the table grows; because of the SEEK behaviour...

improve query performance on SQL Server table contain 3.5 Million rows and growing

2 Answers2