Sunday, March 11, 2012

Another question please

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:

Column Name

Data Type

Length

Allow Nuls

1

msrepl_tran_version

uniqueidentifier

16

0

0

[time]

GMTtime (int)

4

0

0

msec

int

4

1

0

offset

GMToffset (smallint)

2

0

0

type

nameType (varchar)

15

1

0

host

nameType (varchar)

15

1

0

process

nameType (varchar)

15

1

0

dbName

nameType (varchar)

15

1

0

ptName

nameType (varchar)

15

1

0

description

descType (varchar)

47

1

0

rtuName

nameType (varchar)

15

1

0

groupName

nameType (varchar)

15

1

0

message

msgType (varchar)

131

0

0

fgInt

tinyint

1

1

0

sevInt

tinyint

1

1

0

key1

int

4

1

0

key2

int

4

1

0

spooler

tinyint

1

1

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

|||The help is VERY much appreciated!

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

No comments:

Post a Comment