Query taking to long to finish

Transact-SQL
1

Dear all,

Where i work we use a SQL-server that stores alarms generated by the factory.
If i query the database i can get,among other things, the name of the alarm [SOURCE], Time [Timestamp] and what state the alarm is in [ModeStatName]. 1 the alarm is on 0 it is off.

For example:
10:00:00 Temperature furnace, 0
10:02:00 Temperature furnace, 1
10:04:00 Temperature furnace, 0
10:06:00 Temperature furnace, 1

What i would like to get from the database is the alarms that are still active.
So in this case the query would return Temperature furnace.
If i ran it at 10:05:00 it would not return it.
I tried a lot of query's, i will paste one of them:

SELECT TOP 10 a.[Source],a.[TimeStamp],a.[ModeStatName]
FROM database a
LEFT OUTER JOIN database b
ON a.[SOURCE] = b.[Source]
AND a.[TimeStamp]< b.[TimeStamp]
WHERE b.[Source] IS NULL
AND a.[Timestamp] > '2018-09-20'
ORDER BY a.[TimeStamp]

To explain i used the same database for a and b.
I adapted a query i found on the forum.

This query returns the alarms. The problem is it takes 50 minutes to run.
I'm not sure if the query stops after getting the 10 hits. If so it will take a lot longer since we are talking about around 10000 sources. Another problem is this looks at the alarms generated for the last 4 days (20th of september). I need to query 6 months of data.

I found another query that takes the latest timestamp for each source and returns the state it is in then.
I think this might work better but i can't get it to work.

Thanks for your help. Let me know if you need more info!

2

Hi badeend,

pls try my solution:

/*sample DB table definition*/
CREATE TABLE dbo.alerts(
 ID INT IDENTITY(1,1) PRIMARY KEY,
 [Source] VARCHAR(50) NOT NULL,
 [TimeStamp] DATETIME NOT NULL,
 ModeStatName INT NOT NULL
 );

INSERT INTO dbo.alerts ([Source], [TimeStamp], ModeStatName)
 VALUES
 ('Temperature furnace', '2018-09-12 10:00:00', 0),
 ('Temperature furnace', '2018-09-12 10:02:00', 1),
 ('Temperature furnace', '2018-09-12 10:04:00', 0),
 ('Temperature furnace', '2018-09-12 10:06:00', 1),
 ('Air problem 1st floor', '2018-09-23 16:00:00', 1),
 ('Air problem 1st floor', '2018-09-23 17:00:00', 0),
 ('Air problem 2nd floor', '2018-09-24 08:00:00', 1);
 
 
--select * from dbo.alerts;




select [Source], max([TimeStamp]) as LastTimeStamp
into #last_alerts
from dbo.alerts
group by [Source];

create index idxqq01 on #last_alerts([Source], LastTimeStamp);


select *
from dbo.alerts al
    inner join #last_alerts la on la.[Source] = al.[Source]
        and la.LastTimeStamp = al.[TimeStamp]
where al.ModeStatName = 1;

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler

3

could you please explain why you are joining same table to itself? Do you have some sort of hierarchical data?
10000 sources, you mean data rows? If so that is very small amount of data these days.
you say " alarms generated for the last 4 days...I need to query 6 months of data."
you cannot get 6 month worth of data because you have this filter AND a.[Timestamp] > '2018-09-20'

4

Here is a possible solution:

  With alarmData
    As (
Select a.Source
     , a.TimeStamp
     , a.ModeStatName
     , rowNumber = row_number() over(Partition By a.Source Order By a.TimeStamp desc)
  From database a
 Where a.TimeStamp >= dateadd(day, 1, eomonth(current_timestamp, -7))
       )
Select *
  From alarmData ad
 Where ad.rowNumber = 1  -- latest timestamp for each source
   And ad.ModeStatName = 1 -- active alarms

Not sure how well this will process - but give it a try...