March 23, 2012 at 3:46 am
Hi all,
Is there is any performance issue if most of the table in a db have triggers..
Regards
Guru
March 23, 2012 at 4:16 am
It depends on many things, how many triggers, how well written, data types, how many tables they are hitting the list goes on.
I will say that yes a trigger does come with an overhead how much depends very much on the above
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 23, 2012 at 4:25 am
Of course there is. Depending on what action(s) they cover on the table the code potentially has to process in full or partially. They usually include reads/writes to the same or different tables so there is the additional IO over ahead too.
The more you have, the more the overhead.
That said, moving the code into a stored procedure doesnt reduce the overhead it just moves it and makes it more "visible".
March 23, 2012 at 4:26 am
Hi Andy,
Every transaction table have trigger. And it will affect only one table (for audit purpose)
Create TRIGGER [dbo].[tra]
ON [dbo].[a]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @inserted int = (SELECT COUNT(1) FROM inserted);
DECLARE @deleted int = (SELECT COUNT(1) FROM deleted);
DECLARE @auditTime datetimeoffset(7) = sysdatetimeoffset()
IF @inserted > 0 AND @deleted = 0
BEGIN
INSERT Archive.a(AuditType, AuditDate, COL1, ID, rr, test)
SELECT 'Inserted', GETDATE(), COL1, ID, rr, test
FROM inserted;
END
IF @inserted > 0 AND @deleted > 0
BEGIN
INSERT Archive.a(AuditType, AuditDate, COL1, ID, rr, test)
SELECT 'Update (before)', GETDATE(), COL1, ID, rr, test
FROM deleted;
INSERT Archive.a(AuditType, AuditDate, COL1, ID, rr, test)
SELECT 'Update (after)', GETDATE(), COL1, ID, rr, test
FROM inserted;
END
IF @inserted = 0 AND @deleted > 0
BEGIN
INSERT Archive.a(AuditType, AuditDate, COL1, ID, rr, test)
SELECT 'Delete', GETDATE(), COL1, ID, rr, test
FROM deleted;
END
END;
This is the sample trigger
Regards
Guru
March 23, 2012 at 4:27 am
Andy Hyslop (3/23/2012)
It depends on many things, how many triggers, how well written, data types, how many tables they are hitting the list goes on.I will say that yes a trigger does come with an overhead how much depends very much on the above
Andy
If there's code in there, it has an overhead no matter what it does. It could be CPU cycles or IO.
It may only be a small overhead but if its executed 10,000 a second it all adds up.
March 23, 2012 at 4:30 am
Thank you MysteryJimbo,
So its better to use Sp's for inserting into audit tables.
Regards
Guru
March 23, 2012 at 4:31 am
MysteryJimbo (3/23/2012)
Andy Hyslop (3/23/2012)
It depends on many things, how many triggers, how well written, data types, how many tables they are hitting the list goes on.I will say that yes a trigger does come with an overhead how much depends very much on the above
Andy
If there's code in there, it has an overhead no matter what it does. It could be CPU cycles or IO.
It may only be a small overhead but if its executed 10,000 a second it all adds up.
Yep thats exactly what I said 😉
It comes with an overhead of course - but how much depends..
I will say that yes a trigger does come with an overhead how much depends very much on the above
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 23, 2012 at 4:31 am
GuruGPrasad (3/23/2012)
Hi Andy,Every transaction table have trigger. And it will affect only one table (for audit purpose)
This is the sample trigger
Regards
Guru
To start with, you are better off with three seperate triggers making these lines redundant and reducing "overhead"
DECLARE @inserted int = (SELECT COUNT(1) FROM inserted);
DECLARE @deleted int = (SELECT COUNT(1) FROM deleted);
IF @inserted > 0 AND @deleted = 0
BEGIN
END
IF @inserted > 0 AND @deleted > 0
BEGIN
END
IF @inserted = 0 AND @deleted > 0
BEGIN
END
END;
March 23, 2012 at 4:33 am
GuruGPrasad (3/23/2012)
Thank you MysteryJimbo,So its better to use Sp's for inserting into audit tables.
Regards
Guru
Audit purposes is probably one of the few cases I'd stick with triggers as it prevents people "forgetting" (intentionally or not).
I prefer to keep business logic at the stored procedure layer
March 23, 2012 at 4:35 am
I'd stick with triggers as it prevents people "forgetting" (intentionally or not).
Agreed 😀
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 23, 2012 at 4:39 am
Thank you MysteryJimbo
Regards
Guru
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply