We keep a database of events that are classified by severity. I've got a good, efficient query that gives me the grand total for these events. And I have a bad slow query that breaks down the information into daily totals.
My fast query will count the totals for 3213586 events in 4 seconds. The slow daily query takes about 60 seconds for each day.
Please help optimize my slow query!
Database schema is:
- The database view eventView that's used by my queries:
SELECT DATEADD([second], [time] + 60 * offset, '01/01/70') AS timeStr, msec, host, process, dbName, ptName AS point, description, rtuName AS rtu, groupName, message, type, sevInt, [time]
FROM dbo.summary
- Fast totals query:
select
(select count(*)
from [event].[dbo].[eventView]) as Events,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1)as Low,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2)as Medium,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3) as High,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4) as Low1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6) as Medium1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8) as High1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9) as High2
- Slow daily query:
declare @.myDay datetime
declare @.begDay datetime
declare @.myTable
table( myDay datetime,
Events int,
Low int, Medium int,
High int, Low1 int, Medium1 int,
High1 int, High2 int )
select @.myDay = getdate()
select @.begDay = convert(datetime, '10/01/2006')
while @.begDay <= @.myDay
begin
insert into @.myTable (myDay) values (convert(varchar(20), @.begDay, 101))
update @.myTable set Events =
(select count(*) as Events
from [event].[dbo].[eventView]
where
convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
update @.myTable set Low =
(select count(*) as Low
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
update @.myTable set Medium =
(select count(*) as Medium
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
update @.myTable set High =
(select count(*) as High
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
update @.myTable set Low1 =
(select count(*) as Low1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
update @.myTable set Medium1 =
(select count(*) as Medium1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
update @.myTable set High1 =
(select count(*) as High1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
update @.myTable set High2 =
(select count(*) as High2
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)
select @.begDay = dateadd(day,1,@.begDay)
end
select * from @.myTable
Try this is a replacement for your fast query.
select Count(*) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'
It should return the same results, but I bet it's at least 10 times faster.
I'm looking at your slow query now. I would bet there it can be improved a lot. One thing I see is that you are doing a lot of converts, which appear to be unnecessary. For example... where myDay = convert(varchar(20), @.begDay, 101) myDay is a date time field (with time removed/set to 0). @.begDay is also a DateTime variable with time removed. Because of this, you should be able to remove the convert from your where clauses.
|||I'm not sure, but this may be a suitable replacement for the slow query. Please double and triple check the results before putting this in to production.
select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
Count(*) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
I would be interested to know if this works for you, and also what the difference in time is between your query and the one I posted here.
|||Shazam! This worked like a charm - I'm stunned really.
Processed the 232 day's worth of data in my system in 6 seconds.
That other one takes about 60 seconds / day's data - so roughly 4 HOURS to do that same job.
I know I'm just a hacker when it comes to doing this stuff. Thanks a LOT for the help. This makes sense to me!
|||The events are counted using a different criteria - they should be calculated just as a count of all the daily messages, not restricted by the where clause.
select count (*) from [event].[dbo].[eventview] as Events
With the code you are suggesting Events are counting all the messages that are of Type = 'alarm'. How to specify a different critieria within that same query?
|||Are you saying this is missing?
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
Why not add it to mastros' query and then throw that into a stored proc? Just like you started with, but with mastros' query.
|||Instead of "Count(*) As Events", use a sub-query like:
select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
(select count (*) from [event].[dbo].[eventview]) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
I've been struggling with this one for weeks.
What's a good way to learn more about SQL?|||Pick up a SQL book, take a class, and/or hang out on this forum!
Really, though, the best way to learn SQL is to keep working with your data, day in and day out.|||
Making that change (counting events as a sub query) gives me the total of all events.
Sorry - I need a daily total and I'm not clear how to couple that back into the rest of the query.
|||Instead of putting type='alarm' in the where clause, put it in the case statements, like this...
select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
count (*) As Events,
Sum(Case When SevInt = 1 And Type='alarm' Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 And Type='alarm' Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 And Type='alarm' Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 And Type='alarm' Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 And Type='alarm' Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 And Type='alarm' Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 And Type='alarm' Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Your performance may drop a little because you are processing more rows, but some times that is necessary to get the correct data.
|||Yes it is a tad slower - 8 seconds to query the database versus 6 in the earlier one...|||Do you get the correct results?|||It gets the job done, thanks.
I still don't quite understand this "add another query with different criteria" thing. So I'm going to work on this some more until I can figure out how to get an additional daily value for
select count(*) from [event].[dbo].[eventView] where [message] not like '%NORMAL state%' and [message] not like '%restored - normal%' and [message] not like '%communication%restored%' and [message] not like '%PLM - NORMAL%' and [type] = 'alarm' as Alarms
sql