Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The Issue:

I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are sending many measurements in different intervals on different times. I want to make a query which selects all the most actual ( 'actual' I mean the latest from all measurements made in each day) measurement of each day for 2 months e.g from 2013-01-01 to 2013-02-01.

The problem is that this query takes so much time to go, despite all of the indexes i've made on different columns. I've also created auxiliary table that contains max(MeterDate) and MeasurementsId when the measurement was given. I've noticed that index can't be made on MeterDate because it contains date and time which is not useful for making an index on it. So i converted the MeterDate -> CONVERT(date, MeterDate). I though that after joining The auxiliary table with [dbo].[Measurements] the query would be faster but still query takes more than 12s which is too long for me.

The structure of table:

Create table [dbo].[Measurements]

[Id] [int] IDENTITY(1,1) NOT NULL,
[ReadType_Id] [int] NOT NULL,
[Device_Id] [int] NULL,
[DeviceInterface] [tinyint] NULL,
[MeterDate] [datetime] NULL,
[MeasureValue] [decimal](18, 3) NULL

Every row of Measurements table include measurement value on direct MeterDate e.g. "2008-04-04 13:28:44.473"

Direct select structure:

DECLARE @startdate datetime= '2013-07-01'; 
DECLARE @enddate datetime = '2013-08-01';

SELECT *
FROM [dbo].[Measurements] 
WHERE [MeterDate] BETWEEN @startdate and @enddate 

Does anyone knows how to rebuilt table or add new or add indexes on which column that speed up query a bit ? Thanks in advance for any info.

Edit:

The table that I used was created by this query

with t1 as
(
    Select  [Device_Id], [DeviceInterface],  CONVERT(date,  MeterDate) as OnlyDate, Max(MeterDate) as MaxMeterDate
    FROM [dbo].[Measurements] 
    GROUP BY [Device_Id], [DeviceInterface], CONVERT(date,  MeterDate)
)
Select t1.[Device_Id], t1.[DeviceInterface],t1.[OnlyDate], r.Id  
INTO [dbo].[MaxDatesMeasurements]
FROM t1
JOIN [dbo].[Measurements] as r ON r.Device_Id = t1.Device_Id AND r.DeviceInterface = t1.DeviceInterface AND r.MeterDate = t1.MaxMeterDate

Then I wanted to join the new created table [dbo].[MaxDatesMeasurements] with old [dbo].[Measurements] and select direct rows

DECLARE @startdate datetime= '2013-07-01'; 
DECLARE @enddate datetime = '2013-08-01'; 


Select *
From [dbo].[MaxDatesMeasurements] as t1 
Join [dbo].[Measurements] as t2 on t1.[Id] = t2.[Id] 
WHERE t1.[OnlyDate] BETWEEN @startdate AND @enddate
share|improve this question
    
"despite all of the indexes i've made on different columns" SQL antipattern Lets index all columns because select query will be faster that many will hurt you with insert update en delete querys and it also means you could have an bigger index fragmentation... can you post the created table statement andthe querys you run on that table.. if you combinate MAX with GROUP MySQL really needs an multiple index. for example SELECT * FROM phonebook WHERE firstname LIKE 's%' ORDER BY number the index should be KEY(firstname, number) –  Raymond Nijland Sep 25 '13 at 14:53
    
I posted created table statements in edit post. Thanks for response ! –  KatarzynaMoss Sep 26 '13 at 9:54
    
You have a mysql tag but all these [dbo] smell like SQL-Server. Which of the two DBMS do you use? –  ypercube Sep 26 '13 at 18:58
    
I edited the tags. Correct back in the unexpected case that you do use mysql. –  ypercube Sep 26 '13 at 19:02
    
This article may be useful: simple-talk.com/sql/database-administration/… –  AlexKuznetsov Sep 26 '13 at 20:01
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.