update IIS Log table?

• bp

  SSCarpal Tunnel

  Points: 4656

  More actions

  October 14, 2003 at 11:48 am

  #106734

  Our IIS Server is logging website hits to SQL Server. We are trying to figure out best way to export certain records from this table into another table every 5 minutes. So far, we came up with two options:

  1) in a separate table, store the ID of the last record in the IIS Log table that was exported. Every 5 minutes, export the records that are greater than the last exported ID.

  2) pick one of the columns in the log table to be the "Exported" flag column. After exporting a record from the log table, update that record as exported.

  We are leaning towards #2 but am unsure about any locking issues. AKAIK, IIS only inserts records into that table, never update.

  Any ideas or thoughts?

  Thanks in advance,

  Billy

• K. Brian Kelley

  SSC Guru

  Points: 114532

  More actions

  October 14, 2003 at 11:54 am

  #478192

  I would go with #1.

  #1:

  1 Operation to read all rows > #

  1 Operation to update a single row

  #2:

  1 Operation to read all rows not flagged

  1 Operation to update all rows not flagged

  An index can be put on ID. It's all but useless on the flag (since you'll only have two values).

  K. Brian Kelley

  http://www.truthsolutions.com/

  Author: Start to Finish Guide to SQL Server Performance Monitoring

  http://www.netimpress.com/

  K. Brian Kelley
  @kbriankelley

• bp

  SSCarpal Tunnel

  Points: 4656

  More actions

  October 14, 2003 at 12:26 pm

  #478201

  I see... thanks... the query cost of option 1 is 15% compared to 85% of option 2...

  USE TEMPDB

  GO

  SET NOCOUNT ON

  CREATE TABLE TABLE_A(THE_ID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_TABLE_A PRIMARY KEY, THE_VALUE CHAR(1), IS_EXPORTED bit);

  SET NOCOUNT OFF

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',0)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',0)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',0)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',0)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',0)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',1)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',1)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',1)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',1)

  INSERT INTO TABLE_A(THE_VALUE, IS_EXPORTED) VALUES('X',1)

  select * from table_a where the_id > 6;

  select * from table_a where IS_EXPORTED = 1;

  DROP TABLE TABLE_A

  go