May 21, 2003 at 5:58 pm
Hi!
Scenario: We're using a c++ app to store phone states in a SQL 2k (E.G. login, break, work, vacant, logout, etc). As an event is written we're timestamping it with the time the event occured. An agent can only be in one state at one time (I.E. Once they login, they change to lunch when the go on lunch, change to logout when they logout).
Management has requested many reports, some of which are: How long an agent was in break mode, how long was an agent logged in, etc.
We're generating these reports one of two ways:
1] Using a select statement to load the data for an agent during a specified time range into an array and parsing through the array to calculate how long an agent was in a given state. For the most part we can say that the beginining of one state is the ending of another state. We then use a datediff to calculate how long any given state was. The disadvantage to this is there will be a fair amount of recursion in the code.
2] We built a trigger that fires on INSERT and UPDATES the main table's timeendstamp field. This field is null by default but since the beginning of a new state signifies the end of another state, we go back and stamp the previous states endtime. We're referencing the INSERTED table in the trigger. The advantage to this is we don't have to recurse in code. The disadvantage is that we're getting the occasional deadlock error in the c++ app that writes to the database.
Questions:
--We've also written the trigger as a stored procedure. Any advantage to this over the trigger?
--I've considered creating what I call 'state tables'. In other words I create several seperate tables called break, login, work. I then still write the event to the main table but if the event is a break, I write the timeendstamp to the 'break' table using a foreign key relationship. This might have the advantage of using an INSERT instead of an UPDATE to the main table (and possibly not locking the main table). Another advantage might be that I don't have to hit the main table with a query about breaks unless I wanted a lot of details which would come at the cost of a join to the main table. Any advantages to this method?
I hope I've provided enough information without being too verbose. We're relatively new to SQL and welcome all advice. We're hoping to build this correctly the first time.
Thanks for reading!
May 22, 2003 at 6:19 am
Just some suggestions / remarks.
1. You can get the time someone spent in a certain state just from inserting the 'starttime' for each state by using a query like the following.
SELECT s1.Agent, s1.State, s1.Start,
min(s2.Start) As End
FROM State s1
INNER JOIN State s2
ON s1.Agent = s2.Agent
AND s1.Start < s2.Start
(Did not test this, nor did I look at performance).
2. Seems odd that you get deadlocks when you use a trigger. Are you sure there is no recordset left open at the time of the insert that should have been closed?
Be careful with the trigger solution. If you ever do batch inserts of more than one record, the trigger will only be fired once. So you have to make sure you cover this situation in the trigger.
If you don't mind the slower performance / higher load of the first solution, there is no need to go back and update the timeendstamp.
3. Advantage of a stored procedure over a trigger? No real advantage, except for the general advantages a stored procedure has over immediate table access : security (and performance due to cached execution plan).
4. Your 'state' tables look a lot like building a cube. Did you consider using the OLAP possibilities of SQL Server (or any other tool like Business Objects or Cognos) do get your stuff done?
Edited by - NPeeters on 05/22/2003 06:19:56 AM
May 22, 2003 at 11:23 am
Hmmm...I hadn't considered building a cube. I guess I'll do some reading up on this idea.
At one point the c++ app was entering duplicates in the database. While we worked to elminate the problem I first removed the duplicates with a self join much like you described but ended up using a MAX(timestamp) and a GROUP BY agent HAVING COUNT > 1. I may revisit the idea for the timestamp, however. Given that ultimately we'll be storing a lot of data it seemed best to try to get the data as soon as it was entered (via the trigger) instead of trying to obtain the data as it was needed.
Thanks for the good ideas! They are appreciated!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply