0

I’m designing a database with a table that automatically gets loaded with a new record every 15 seconds (each record will have a 'timestamp' field and around 50 ‘float’ fields). Each record in this table needs to be copied to another table. I have done some initial testing with two different approaches to do this:

  1. Use a trigger to insert each record into the other table (will fire every time a record is added to the source table - every 15 seconds)

  2. Use a job (SQL Server Agent) to run every ten minutes or so and insert a batch of records into the other table (and use a flag field in the source table to indicate which records have been processed).

I don’t have very much experience with triggers or SQL Server jobs so I don’t know the impact regarding overhead and system resources (over time, both tables will have a lot of records).

In general, what approach would be better? Is there another approach I should consider?

Thanks

  • One query per 15 second? From the point of the performance it doesn't matter what method to use. But when you use a job, you have a long enough period when the data is in the main table, but not in the copy. From the point of view of the data actuality the trigger is preferred. – Akina Apr 16 at 5:26
  • Do you have control of the application writing the records into the first table? Can you refactor that app to write the record into both tables instead? – HandyD Apr 16 at 5:31
  • No I Haven't Control and I Can't Refactor App @HandyD – mehdi.mahdavi Apr 16 at 5:35
  • In that case, you need to determine the requirement for data to be up to date in the 2nd table. One new record every 15 seconds means a job on a ten-minute schedule will be 40 records behind. Over time, retrieving these 40 records (with their 50+ columns) might become a performance problem. In this case, a trigger will potentially work better as it will keep the second table up to date, however, there is always the impact to insert performance on the first table that this will cause. – HandyD Apr 16 at 5:38
  • Than you @HandyD – mehdi.mahdavi Apr 16 at 6:44
1

When inserting one record each 15 seconds I would not be worried about using triggers.

Consider these two heap tables, with one rowversion column and 50 float columns each.

CREATE TABLE dbo.floattable1
(Rowver ROWVERSION
,Float1    float
,Float2    float
...);
CREATE TABLE dbo.floattable2
(Rowver ROWVERSION
,Float1    float
,Float2    float
...);

Both tables have ~2M rows (1940480).

Solution with a trigger

When we create an AFTER INSERT TRIGGER on dbo.floattable1 to also insert into dbo.floattable2

CREATE TRIGGER dbo.floattrigger
ON dbo.FloatTable1
AFTER INSERT
AS
INSERT INTO dbo.floattable2(Float1    ,Float2     ,Float3     ,Float4     ,Float5     ,Float6     ,Float7     ,Float8     ,Float9     
,Float10  ,Float11  ,Float12  ,Float13  ,Float14  ,Float15  ,Float16  ,Float17  ,Float18  ,Float19  ,Float20  ,Float21  ,Float22  
,Float23  ,Float24  ,Float25  ,Float26  ,Float27  ,Float28  ,Float29  ,Float30  ,Float31  ,Float32  ,Float33  ,Float34  ,Float35  
,Float36  ,Float37  ,Float38  ,Float39  ,Float40  ,Float41  ,Float42  ,Float43  ,Float44  ,Float45  ,Float46  ,Float47  ,Float48  ,Float49  ,Float50 )
SELECT Float1     ,Float2     ,Float3     ,Float4     ,Float5     ,Float6     ,Float7     ,Float8     ,Float9     ,Float10  ,Float11  ,Float12  ,Float13  
,Float14  ,Float15  ,Float16  ,Float17  ,Float18  ,Float19  ,Float20 ,Float21  ,Float22  ,Float23  ,Float24  ,Float25  ,Float26  ,Float27  ,Float28  ,Float29  
,Float30  ,Float31  ,Float32  ,Float33  ,Float34  ,Float35  ,Float36  ,Float37  ,Float38  ,Float39  
,Float40  ,Float41  ,Float42  ,Float43  ,Float44  ,Float45  ,Float46  ,Float47  ,Float48  ,Float49  ,Float50 
FROM inserted;

Afterwards, we insert one whole row in dbo.floattable1 resulting in trigger execution:

enter image description here

And a very simple plan with 1 logical read.

Solution for the job / FlagColumn

Drop the trigger, add a flagcolumn, set it to 1 and add an index on it.

DROP TRIGGER  dbo.floattrigger;
ALTER TABLE dbo.floattable1 
ADD FlagColumn bit;
UPDATE dbo.floattable1 SET FlagColumn = 1;

CREATE INDEX IX_FlagColumn on dbo.floattable1(FlagColumn);

Insert 40 test records with flagcolumn = 0

SET STATISTICS IO, TIME ON;
INSERT INTO dbo.floattable1(Float1    ,Float2     ,... ,FlagColumn)
SELECT TOP(40) Float1     ,Float2     ,... , 0
FROM dbo.floattable1

Insert into dbo.floattable2 and update the flagcolumns

BEGIN TRANSACTION
INSERT INTO dbo.floattable2
(Float1   ,Float2     ,...)
SELECT Float1     ,Float2     ,...
FROM dbo.floattable1 
WHERE FlagColumn = 0;

UPDATE dbo.floattable1 
SET FlagColumn = 1
WHERE FlagColumn = 0;
COMMIT TRANSACTION

The first insert can be improved to be equal to the trigger's insert, by removing the RID lookup (adding all floats to the included column list of the index).

enter image description here

But the update will give some extra overhead.

enter image description here.

The advantage you have here is that you can schedule this when you want, and have some more information on your data via the flag. The extra index means extra overhead and Insert + Update means extra overhead than only issueing an Insert.


Both ideas are viable, but it will really depend on how complex / simple the table is used. If it is only used as a logging table, and the trigger will fire for one record each 15 seconds (and nothing else), the trigger should be fine. If the data sync does not need to happen that fast / often, data load with an INSERT + UPDATE should be fine too. YMMV, always test before implementing.

0

Consider this point,

Suppose 5 records(more than 1 record) is inserted every 15 second. Then Trigger will fire for those many rows.Which means that many database logs

On other hand,Jobs will insert those 5 records in single transaction which reduce the entry of database log.

So in certain situation Trigger are expensive resources than Jobs.

If single record is inserted every 15 second and no other insert or transaction happen in any other table then Trigger is acceptable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.