Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Most efficient FIFO logging.

    My buddy has an application that logs entries, and for space reasons needs to only retain a maximum N records in the log table. He wants to delete old log entries as part of the insert procedure. Here is his stab at it:
    Code:
    CREATE PROCEDURE spInserttblLog
    	@Message varchar(1024),     
    	@LogDate datetime,     
    	@ElapsedSeconds float,     
    	@LogLevel varchar(50),     
    	@UserName varchar(100),     
    	@ProcessName varchar(100),     
    	@MachineName varchar(100),     
    	@MaxEntries int=0
    AS     
    declare @ varchar(300)     
    if @MaxEntries > 0     
    	Begin          
    	set @MaxEntries = @MaxEntries -1          
    	set @SQL='Delete From tblLog Where LogID Not In (Select Top ' + cast(@MaxEntries as varchar) + ' LogID from tblLog order by LogID desc)'          
    	execute (@SQL)     
    	End
    
    insert into tblLog
    	(Message,
    	LogDate,
    	ElapsedSeconds,
    	LogLevel,
    	UserName,
    	ProcessName,
    	MachineName)     
    values	(@Message,
    	@LogDate,
    	@ElapsedSeconds,
    	@LogLevel,
    	@UserName,
    	@ProcessName,
    	@MachineName)
    I think this would be more efficient:
    Code:
    --SQL Server
    CREATE	PROCEDURE spInserttblLog
    	@Message varchar(1024),     
    	@LogDate datetime,     
    	@ElapsedSeconds float,     
    	@LogLevel varchar(50),     
    	@UserName varchar(100),     
    	@ProcessName varchar(100),     
    	@MachineName varchar(100),     
    	@MaxEntries int=0
    AS
    
    delete
    from	tblLog
    where	LogID < (select max(LogID) from tlbLog) - @MaxEntries - 2
    	and @MaxEntries > 0
           
    insert into tblLog
    	(Message,
    	LogDate,
    	ElapsedSeconds,
    	LogLevel,
    	UserName,
    	ProcessName,
    	MachineName)     
    values	(@Message,
    	@LogDate,
    	@ElapsedSeconds,
    	@LogLevel,
    	@UserName,
    	@ProcessName,
    	@MachineName)
    Comments, or suggestion for an even faster method? This is a relatively high-activity table, so there are a lot of inserts.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Blindman

    Assuming @MaxEntries is > 0 when the proc is first run (and the plan cached) - would this have a detremental affect on your sproc if it is subsequently not supplied\ supplied as 0? There is some overhead for the DELETE statement? Better to test with IF as your buddy - virtually no overhead?

    Also - how come this is called as part of the insert? Would it not be better as a regular, separate job? And why a varying number of records?

    Otherwise - I like yours better
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Is your friend's table busy 24 / 7 or is there some slack time?

    If there is some slack I would use a 2 phase approach. Let the sproc insert the row, capture the scope_identity(), and place it with a datetime into another table.

    Then, during db maintenance time, run a job to do the delete at say 5000 a clip to avoid locking the table for long periods of time using the maxcount variable..

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I agree that this sort of thing is better run as a separate maintenance procedure than as part of the insert. But this is not a dedicated SQL Server application. It has versions for SQLSVR, Oracle, MS Access, and MySQL. The hassle of setting up independent and automated maintenance routines may outweigh the concerns of bundling these two operations in a single procedure.
    Regardless, I propose this just as an interesting problem, and I'm keen on seeing everyone's opinions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I just can't see use of Exec on an SQL statement incorporating an IN clause being as efficient as yours.

    I do hope you trust your FE developers to not miss a zero or three off the @MaxEntries parameter too
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •