How to retrieve LastEvent in Object/Event Model

• Oran Levin

  Grasshopper

  Points: 10

  More actions

  December 6, 2007 at 10:03 am

  #122547

  Hi,

  Facts:

  I created a database to support an application that tracks events on different objects. The two main tables are tbl_Object and tbl_EventLog. Each table has unique ID and on the tbl_EventLog there is FK for a record in the tbl_Object. The events are inserted all the time for the same or different objects from the tbl_Object. There are about 600,000 objects in the tbl_Object and 1,500,000 (and growing) events in tbl_EventLog.

  Question:

  The user often wants to know what the last event was for a specific object.

  What is the best way of retrieving the last event?

  Should I simply do a max(eventdatetime) on a specific object? or Should I add a LastEventID column to tbl_Object and update it every time a new event is inserted? or any other way to implement it?

  I chose the second method because I didn't think it made sense search the event table everytime the user wants to know the last event, but I wanted to know what the experts thought.

  Please let me know what you think.

  Thank you,

  Oran Levin

• Robert Page-349193

  Valued Member

  Points: 65

  More actions

  January 2, 2008 at 11:24 am

  #764140

  I'm not an expert by any means but what I would have done here is built the following stored procedure and use it to retrieve the event details when needed.

  create procedure proc_get_last_event_details @object_id integer

  as

  set nocount on

  select e.column1, e.column2, e.column3

  from tbl_eventlog e

  where unique_id = (select max(unique_id)

  from tbl_eventlog

  where e.object_id = @object_id)

  go

  Using the stored procedure instead of creating and maintaining a LastEventID on the tbl_Objects table just seems cleaner to me. If you already have a unique id on the table, use that instead of the timestamp (it's already indexed, assuming you set it as your pk)

  With a stored procedure, you are:

  - pulling the eventlog details with a stored procedure

  With the field you are:

  - storing additional data (even though it may never be used.)

  - updating the column with every event via a trigger or stored procedure

  - still pulling the eventlog details with a stored procedure

  ---

  Kindest Regards,

  Michael Page
  

• Oran Levin

  Grasshopper

  Points: 10

  More actions

  January 2, 2008 at 11:41 am

  #764147

  Hi Michael,

  Thanks for the response.

  I didn't think about using the tbl_EventLog PK to do the MAX() on to retrieve the last event. It sounds like a good suggestion.

  If you are interested I posted this question in another group and had some good responses. Here is the link...

  Thanks,

  Oran Levin

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 2, 2008 at 12:00 pm

  #764156

  This query should do what you want.

  selecttop 1

  LastEventID = e.EventID

  from

  tbl_eventlog e

  where

  e.object_id = @object_id

  order by

  e.EventDateTime desc

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736568

  More actions

  January 2, 2008 at 12:31 pm

  #764164

  I was going to suggest what Michael posted.

  Since I often find myself going back a few events, I'd actually keep a query around to pull everything from today.

  select

  ...

  where date > '1/2/07'

  so I could see what was happening prior to the event. If it's a lot, you could always change Michael's to be top 10, etc.