Help with group by on log history table, optimization and gaps - Aaron Bertrand [SQL Server MVP]

So use a calendar table!  Then you can just join against a range instead of
building a union.
http://www.aspfaq.com/2519


A better way, IMHO?

DECLARE @today SMALLDATETIME;
SELECT @today = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);

Also, don't do this:


If you have values where the time is midnight, you will double-count those
rows in this day's calculations *and* the next day's.  Much better to do:

[Date] >= t.[date] AND [Date] < (t.[date] + 1)


I think there is a much better way to get these counts than to attack the
table in four separate queries.  Why not (assuming you built a calendar
table):

DECLARE @today SMALLDATETIME;

SET @today = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);

SELECT
[date] = c.dt,
L.[Level],
COUNT(L.[Level])
FROM
dbo.Calendar c WITH (NOLOCK)
LEFT OUTER JOIN dbo.[log] L WITH (NOLOCK)
ON L.[date] >= c.dt AND L.date < (c.dt + 1)
WHERE
c.dt >= (@today - 14) AND c.dt <= @today
L.[Level] IN ('INFO', 'WARN', 'ERROR', 'FATAL')
GROUP BY
c.dt,
L.[Level];

And then you can pivot that in the application to make it look like columns
instead of rows (and add a right outer join to a calendar table to fill in
gaps).  I could show you ways to pivot in T-SQL in SQL Server 2005, or using
a CTE perhaps, but you forgot to tell us which version of SQL Server you are
using.

Help with group by on log history table, optimization and gaps - Ramon

We have a table were we store log4net entries with a datetime.

I created a query that returns a resultset per day of the last 14 days
where each log entry type is summed.

1. When I just look at the query it seems that is can be optimized
*heavily* but I don't know how.

2. When no log entries are inserted on a day then this day does not
show up in the results. I think i first need to create a table for the
last fourteen days just as I'm doing now but then not based on the log
table. I thought of doing a simple union for each day but maybe there
are easier ways to create such a temporary resultset.


This is my current query:

SELECT
[date]
,INFO = (select count(*) from [log] where [Level]='INFO' AND [Date]
between t.date and t.date+1)
,WARN = (select count(*) from [log] where [Level]='WARN' AND [Date]
between t.date and t.date+1)
,ERROR = (select count(*) from [log] where [Level]='ERROR' AND [Date]
between t.date and t.date+1)
,FATAL = (select count(*) from [log] where [Level]='FATAL' AND [Date]
between t.date and t.date+1)
FROM
(SELECT
CAST(FLOOR( CAST( [date] AS FLOAT ) ) AS DATETIME) as [date]
FROM
[Log]
WHERE
[date]> (CAST(FLOOR( CAST( getdate() AS FLOAT ) ) AS DATETIME) -14)
AND [Level]='INFO'
GROUP BY
CAST(FLOOR( CAST( [date] AS FLOAT ) ) AS DATETIME)
) t
ORDER BY
[date]

Help with group by on log history table, optimization and gaps - Ramon

As for the gaps... the union approach is working fine although
creating this union looks a bit stupid:

Please note that the previous query contained a bug in the WHERE
clause.

Current query:

DECLARE @today datetime
SELECT @today=CAST(FLOOR( CAST( getdate() AS FLOAT ) ) AS DATETIME)

SELECT
[date]
,INFO = (select count(*) from [log] (nolock) where [Level]='INFO' AND
[Date] between t.date and t.date+1)
,WARN = (select count(*) from [log] (nolock) where [Level]='WARN' AND
[Date] between t.date and t.date+1)
,ERROR = (select count(*) from [log] (nolock) where [Level]='ERROR'
AND [Date] between t.date and t.date+1)
,FATAL = (select count(*) from [log] (nolock) where [Level]='FATAL'
AND [Date] between t.date and t.date+1)
FROM
(SELECT @today as [date]
UNION
SELECT @today-1
UNION
SELECT @today-2
UNION
SELECT @today-3
UNION
SELECT @today-4
UNION
SELECT @today-5
UNION
SELECT @today-6
UNION
SELECT @today-7
UNION
SELECT @today-8
UNION
SELECT @today-9
UNION
SELECT @today-10
UNION
SELECT @today-11
UNION
SELECT @today-12
UNION
SELECT @today-13
UNION
SELECT @today-14
) t
ORDER BY [date]

Help with group by on log history table, optimization and gaps - Razvan Socol

Try something like this:

DECLARE @today datetime
SELECT @today=CAST(FLOOR( CAST( getdate() AS FLOAT ) ) AS DATETIME)

SELECT
t.date
,INFO = SUM(CASE WHEN Level='INFO' THEN 1 ELSE 0 END)
,WARN = SUM(CASE WHEN Level='WARN' THEN 1 ELSE 0 END)
,ERROR = SUM(CASE WHEN Level='ERROR' THEN 1 ELSE 0 END)
,FATAL = SUM(CASE WHEN Level='FATAL' THEN 1 ELSE 0 END)
FROM (
SELECT @today as date
UNION ALL SELECT @today-1
UNION ALL SELECT @today-2
UNION ALL SELECT @today-3
UNION ALL SELECT @today-4
UNION ALL SELECT @today-5
UNION ALL SELECT @today-6
UNION ALL SELECT @today-7
UNION ALL SELECT @today-8
UNION ALL SELECT @today-9
UNION ALL SELECT @today-10
UNION ALL SELECT @today-11
UNION ALL SELECT @today-12
UNION ALL SELECT @today-13
UNION ALL SELECT @today-14
) t LEFT JOIN Log ON Log.Date>=t.date and Log.Date<t.date+1
GROUP BY t.date
ORDER BY t.date

--
Razvan Socol
SQL Server MVP