Harshal Sam Harshal Sam - 2 months ago 19
C# Question

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

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.

Answer

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);