1

I recently created a SQL trigger to replace a very expensive query I used to run to reduce the amount of updates my database does each day.

Before I preform an update I check to see how many updates have already occurred for the day, this used to be done by querying:

SELECT COUNT(*) FROM Movies WHERE DateAdded = Date.Now

Well my database has over 1 million records and this query is run about 1-2k a minute so you can see why I wanted to take a new approach for this.

So I created an audit table and setup a SQL Trigger to update this table when any INSERT or UPDATE happens on the Movie table. However I'm noticing the audit table is getting out of sync by a few hundred everyday (the audit table count is higher than the actual updates in the movie table). As this does not pose a huge issue I'm just curious what could be causing this or how to go about debugging it?

SQL Trigger:

ALTER TRIGGER [dbo].[trg_Audit]
ON [dbo].[Movies]
AFTER UPDATE, INSERT
AS
BEGIN
    UPDATE Audit SET [count] = [count] + 1 WHERE [date] = CONVERT (date, GETDATE())
    IF @@ROWCOUNT=0
    INSERT INTO audit ([date], [count]) VALUES (GETDATE(), 1)
END

The above trigger only happens after an UPDATE or INSERT on the Movie table and tries to update the count + 1 in the Audit table and if it doesn't exists (IF @@ROWCOUNT=0) it then creates it. Any help would be much appreciated! Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bfritz
  • 2,416
  • 2
  • 20
  • 29
  • 1
    Do you realise that if you update 10 records at once this trigger will only increment the count by 1? – OGHaza Feb 10 '14 at 09:34
  • @OGHaza that is fine as I never update or insert more than one record at a time, also the audit table count (sync issue) is higher than what I'm returning when I query my movie table with WHERE DateAdded = Date.Now... This is what I'm trying to figure out – bfritz Feb 10 '14 at 09:37
  • Which means my trigger is being triggered more than the actual amount of updates/inserts into the database. I also tested to make sure when I create and #tempTables it's not triggering and it wasn't that either. – bfritz Feb 10 '14 at 09:44
  • Well in that case something must be doing additional updates on your movies table.. – OGHaza Feb 10 '14 at 09:44
  • When using AFTER UPDATE, INSERT that means it will only trigger after a successful update/insert correct? – bfritz Feb 10 '14 at 09:47
  • Yup, do you change the DateAdded on Update? Because obviously otherwise `WHERE DateAdded = Date.Now` would only return INSERT count – OGHaza Feb 10 '14 at 09:48
  • It would seem like an indexed view built against the `Movies` table would be more sensible - SQL Server would take care of maintaining it automatically, and you could do away with your triggers – Damien_The_Unbeliever Feb 10 '14 at 09:49
  • @Damien_The_Unbeliever that sounds interesting I've never setup a view before in SQL, I'm currently running SQL 2008 and before I went for the trigger route I came across some ways to handle this with the REAL audit feature however that doesn't seem to be installed on my server. – bfritz Feb 10 '14 at 09:53

2 Answers2

3

Something like this should work:

create table dbo.Movies (
    A int not null,
    B int not null,
    DateAdded datetime not null
)
go
create view dbo.audit
with schemabinding
as
    select CONVERT(date,DateAdded) as dt,COUNT_BIG(*) as cnt
    from dbo.Movies
    group by CONVERT(date,DateAdded)
go
create unique clustered index IX_MovieCounts on dbo.audit (dt)

This is called an indexed view. The advantage is that SQL Server takes responsibility for maintaining the data stored in this view, and it's always right.

Unless you're on Enterprise/Developer edition, you'd query the audit view using the NOEXPAND hint:

SELECT * from audit with (noexpand)

This has the advantages that

a) You don't have to write the triggers yourself now (SQL Server does actually have something quite similar to triggers behind the scenes),

b) It can now cope with multi-row inserts, updates and deletes, and

c) You don't have to write the logic to cope with an update that changes the DateAdded value.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Hey Damien, I just tested this on my Dev environment and it seems to be working and updating properly, however I wanted to know if this would have anymore overhead than the trigger function? As you see I had to run some tricks within the trigger to avoid a select statement for checking if the row existed before trying to update. Being I get over 15 million requests a day I need to make sure won't drastically increase my server load. When are these views updated? on demand? or like a trigger after each insert/update? – bfritz Feb 10 '14 at 10:11
  • @bfritz - they're almost exactly like if you'd written a trigger, and the rules around what's allowed in an indexed view are to ensure that they're efficient - i.e. they're designed, explicitly, so that maintaining the index can be achieved based *purely* on the rows affected by a particular statement - they never have to scan the entire table. – Damien_The_Unbeliever Feb 10 '14 at 10:14
  • Just wanted to add I implemented this into production and discovered when not using the "WITH (NOEXPAND)" it was a very slow to query! So thanks again for that! – bfritz Feb 10 '14 at 18:31
  • 2
    @bfritz - yes, without the noexpand hint, it basically ignores all of the work you've done and runs the query over the entire base table, ignoring the index. That's one of the ways that MS try to upsell you to Enterprise Edition. – Damien_The_Unbeliever Feb 10 '14 at 19:05
1

Rather than incrementing the count by 1 you should probably be incrementing it by the number of records that have changed e.g.

UPDATE Audit 
SET [count] = [count] + (SELECT COUNT(*) FROM INSERTED)
WHERE [date] = CONVERT (date, GETDATE())
IF @@ROWCOUNT=0
INSERT INTO audit ([date], [count]) 
VALUES (GETDATE(), (SELECT COUNT(*) FROM INSERTED))
OGHaza
  • 4,795
  • 7
  • 23
  • 29
  • I can't see anything else wrong so until you've tried that I can only assume thats it. That trigger appears to work fine on SQLFiddle. – OGHaza Feb 10 '14 at 09:43