SQL Report/View

[Dan Williams]

We have a bookings table that records various information and for each
booking we record various events that users have performed on that booking
along with when the event occurred.

For example:-

Bookings EventLog
--------- ---------
BookID PK EvID Primary Key
BookDate BookID Foreign Key
BookStart Event
BookFinish, UserID
BookStatus,... EventTime

BookID 1 could have a date of 10/03/05, start time 09:00 and finish time
17:00.
A record gets created in the event table corresponding to which user created
the booking and at what time.
e.g. EvID = 1, BookID = 1, Event = 'Booking Created', UserID = 'dan',
EventTime = '2005/03/09 10:00'

Events get recorded to the EventLog table whenever something happens to the
booking (i.e. the booking status changes, date changes, etc).

We'd like to create a report that calculates the activity of all of our
users. Currently, we loop through all our users and perform individual SQL
queries that count the number of occurrences for a particular event in
between two dates specified.

e.g. SELECT count(EvID) FROM EventLog WHERE UserID = 'dan' AND Event =
'Booking Created' AND EventTime > '2005/03/01' AND EventTime < '2005/03/10'

This is obviously taking a lot of processing power and resources to
calculate and it slows our application down considerably whenever someone
attempts to view it.

Can anyone recommend a decent way to do this? Ideally, when someone wishes
to view this report (via ASP by the way), rather than it having to
recalculate the information using the datefrom and dateto dates they
specify, it should just displayed the information from a pre-calculated
view? In other words, is it possible to generate the reports overnight or
constantly in real-time, given that people will wish to view different
information at different times (i.e. different users and dates?)

Many thanks for any suggestions you can give me.

Dan Williams.

[Erland Sommarskog]

Dan Williams (dt********@hotmail.com) writes:

We'd like to create a report that calculates the activity of all of our
users. Currently, we loop through all our users and perform individual SQL
queries that count the number of occurrences for a particular event in
between two dates specified.

e.g. SELECT count(EvID) FROM EventLog WHERE UserID = 'dan' AND Event =
'Booking Created' AND EventTime > '2005/03/01' AND EventTime <
'2005/03/10'
So what's wrong with

SELECT UserID, Count(EvID)
FROM eventlog
WHERE Event = 'Booking Created'
AND EventTime > '20050301' AND
AND EventTime < '20050310'
GROUP BY UsedID

?
Can anyone recommend a decent way to do this? Ideally, when someone wishes
to view this report (via ASP by the way), rather than it having to
recalculate the information using the datefrom and dateto dates they
specify, it should just displayed the information from a pre-calculated
view? In other words, is it possible to generate the reports overnight or
constantly in real-time, given that people will wish to view different
information at different times (i.e. different users and dates?)

I may have misunderstood the problem, but it seems that the GROUP BY
query would be a good start. For even better response time, you could
consider an indexed view.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lee Tudor]

Your performance problems are probably down to the lack of an appropriate
index (i.e. on EventTime), or maybe that the optimizer is doing its usual
trick of casting the column rather than the constant.

using variables of the same datatype as the column being compared will
ensure that the optimizer doesnt shoot itself in the foot.

SELECT UserID, Count(EvID)
FROM eventlog
WHERE Event = @Event
AND EventTime > @StartTime AND
AND EventTime < @FinishTime
GROUP BY UsedID

covering event and maybe even userID or clustering the eventtime index will
further improve performance but be aware of the knock on storage and
performance implications.

Regards
Mr Tea

"Dan Williams" <dt********@hotmail.com> wrote in message
news:d0*********@hercules.btinternet.com...

We have a bookings table that records various information and for each
booking we record various events that users have performed on that booking
along with when the event occurred.

For example:-

Bookings EventLog
--------- ---------
BookID PK EvID Primary Key
BookDate BookID Foreign Key
BookStart Event
BookFinish, UserID
BookStatus,... EventTime

BookID 1 could have a date of 10/03/05, start time 09:00 and finish time
17:00.
A record gets created in the event table corresponding to which user
created the booking and at what time.
e.g. EvID = 1, BookID = 1, Event = 'Booking Created', UserID = 'dan',
EventTime = '2005/03/09 10:00'

Events get recorded to the EventLog table whenever something happens to
the booking (i.e. the booking status changes, date changes, etc).

We'd like to create a report that calculates the activity of all of our
users. Currently, we loop through all our users and perform individual SQL
queries that count the number of occurrences for a particular event in
between two dates specified.

e.g. SELECT count(EvID) FROM EventLog WHERE UserID = 'dan' AND Event =
'Booking Created' AND EventTime > '2005/03/01' AND EventTime <
'2005/03/10'

This is obviously taking a lot of processing power and resources to
calculate and it slows our application down considerably whenever someone
attempts to view it.

Can anyone recommend a decent way to do this? Ideally, when someone wishes
to view this report (via ASP by the way), rather than it having to
recalculate the information using the datefrom and dateto dates they
specify, it should just displayed the information from a pre-calculated
view? In other words, is it possible to generate the reports overnight or
constantly in real-time, given that people will wish to view different
information at different times (i.e. different users and dates?)

Many thanks for any suggestions you can give me.

Dan Williams.