asked on

SQL Trigger and performance

I have a table called SMS_log that logs SMS messages. If a message is sent correct the "ok" field is True, if it fails the "ok" field is false.

I have a SQL Trigger that check if the message is sent or not. If its not ("ok" = 0) the message shall be sent again (but from another number / system).

My question is:
Can SQL Triggers like mine (see attached code) cause a performance issue? Sometimes we send about 2500 messages at the same time. Is there something in my code that can be optimized to increase the performance?

ALTER TRIGGER [dbo].[trgErrorHandler]
   ON  [dbo].[SMS_Out_Log] 
   for insert
AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @MsgId varchar(18)
	DECLARE @FromNumber varchar(18)
	DECLARE @Tel varchar(18)
	DECLARE @Msg varchar(160)
	DECLARE @PriceGroup varchar(8)
	DECLARE @CampaignId varchar(8)
	DECLARE @Ok bit
	DECLARE @ErrorCode varchar(50)
	DECLARE @SentWithOperator varchar(8)
	DECLARE @Type varchar(18)
	DECLARE @Description varchar(max)

	SET @Ok = (select ok from INSERTED)

	IF (@Ok = 0)
	BEGIN

		SET @FromNumber = (select fromNumber from INSERTED)
		SET @SentWithOperator = (select SentWithOperator from INSERTED)
		SET @ErrorCode = (select ErrorCode from INSERTED)

		IF (@FromNumber = '5100' AND @SentWithOperator = '4711' AND @ErrorCode = '1030')
		BEGIN

			-- Message info
			SET @MsgId = (select msgID from INSERTED)
			SET @Tel = (select Tel from INSERTED)
			SET @Msg = (select msg from INSERTED)
			SET @PriceGroup = (select PriceGroup from INSERTED)
			SET @CampaignId = (select CampaignID from INSERTED)
			SET @Type = (select Optional2 from INSERTED)
			SELECT @Description = Optional1 from SMS_Out_Log where msgID = @MsgId

			-- Resend message from another number
			Insert into SMS_Out 
			(fromNumber, Tel, msg, PriceGroup, Operator, Optional1, Optional2, CampaignID) 
			values 
			('6300', @Tel, @Msg, @PriceGroup, '0', @Description, @Type, @CampaignId)

		END

	END
END

Open in new window

Kyle Abrahams, PMP

SET @FromNumber = (select fromNumber from INSERTED)
            SET @SentWithOperator = (select SentWithOperator from INSERTED)
            SET @ErrorCode = (select ErrorCode from INSERTED)

can be

select @FromNumber = fromNumber, @SentWithOPerator = SentWithOpertor @ErrorCode = ErrorCode
from INSERTED

Also you don't need to go through the trouble of setting all of the variables:

Insert into SMS_Out
                  (fromNumber, Tel, msg, PriceGroup, Operator, Optional1, Optional2, CampaignID)
                  select
                  '6300', Tel,Msg, PriceGroup, '0', Description, Type, CampaignID from inserted

Scott Pletcher

>> Can SQL Triggers like mine (see attached code) cause a performance issue? Sometimes we send about 2500 messages at the same time. <<

Typically that shoudln't cause any problem. However, it could if the table being inserted too had lots of indexes and/or activity on it.

>> Is there something in my code that can be optimized to increase the performance? <<

Yes. Get rid of the variables, instead inserting directly from the inserted table, and specify the owning schema on the table name(s) in the query (usually "dbo.", so I assumed that in my code below). W/o a schema name, typically SQL can't cache the SQL plan and re-use it (altho this is a trivial plan and might not be cached anyway, the general rule still applies:

ALWAYS put schema names on tables in triggers.

ALTER TRIGGER [dbo].[trgErrorHandler]
ON [dbo].[SMS_Out_Log]
AFTER INSERT
AS
      SET NOCOUNT ON
      INSERT INTO dbo.SMS_Out
                  ( fromNumber, Tel, msg, PriceGroup, Operator, Optional1, Optional2, CampaignID )
      SELECT
                  '6300', Tel, msg, PriceGroup, '0', Optional1 AS Description, Optional2 AS Type, CampaignId
      FROM inserted
      WHERE
       Ok = 0 AND
       FromNumber = '5100' AND
       SentWithOperator = '4711' AND
       ErrorCode = '1030'
GO

Banthor

TRIGGERS == BAD, EVIL, NO
STOREDPROCEDURES == GOOD, HEAVEN, BLISS

Scott Pletcher

COMMENTS == WRONG WRONG WRONG

webressurs

ASKER

Hi!

Thanks for all help. If you see my example code I cannot do a "select from inserted" for the "Optional1" field. The reason for that is because this field has DataType "Text", and a "select from inserted" on this DataType will fail. That's why I did like this:

SELECT @Description = Optional1 from SMS_Out_Log where msgID = @MsgId

Open in new window

Since the trigger runs each time a message is sent (sometimes 2500 messages are sent at the same time) I don't want to do the SELECT above for each message if not necessary. I only need the @Description value if I do a INSERT INTO dbo.SMS_Out. Is there a "smart" way to include this in your example, ScottPletcher?

One more thing: Messages can have different kind of error codes. In my example I could make different IF/ELSE statements based on the error codes (variables). If I need to do different things based on the errorcodes using your example, do you think this is the best way:

      INSERT INTO dbo.SMS_Out 
                  ( fromNumber, Tel, msg, PriceGroup, Operator, Optional1, Optional2, CampaignID )  
      SELECT
                  '6300', Tel, msg, PriceGroup, '0', Optional1 AS Description, Optional2 AS Type, CampaignId
      FROM inserted
      WHERE
          Ok = 0 AND
          FromNumber = '5100' AND
          SentWithOperator = '4711' AND
          ErrorCode = '1030'


      UPDATE tblUser where 
          cellphone = Tel from inserted AND -- (Dont know if this is correct?)
          Ok = 0 AND
          FromNumber = '2500' AND
          SentWithOperator = '4710' AND
          ErrorCode = '1040'

Open in new window

Thanks alot :)

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

webressurs

ASKER

Thank you so much, I really appreciate :)

Just one final question regarding the Text coloumn. You write that it will be slightly less efficiently since it will have to lookup everyone separately. Since the trigger never shall run if Ok = 1, can the performance be increased by only running the code when Ok = 0 ?

Something like:

IF (Ok from inserted = "0")
BEGIN


      INSERT INTO dbo.SMS_Out 
                ( fromNumber, Tel, msg, PriceGroup, Operator, 
                  Optional1, 
                  Optional2, CampaignID )  
      SELECT
                  '6300', Tel, msg, PriceGroup, '0', 
                  (SELECT CAST(Optional1 AS varchar(max)) FROM dbo.SMS_Out_Log sol WHERE sol.msgID = i.msgID) AS Description, 
                  Optional2 AS Type, CampaignId
      FROM inserted i
      WHERE
          FromNumber = '5100' AND
          SentWithOperator = '4711' AND
          ErrorCode = '1030' 


      UPDATE u...


END

Open in new window

Scott Pletcher

But what if they insert 100 rows in one statement, and 50 are OK and 50 are not OK?

webressurs

ASKER

Hi! There is only inserted one row at the time. If we send 100 messages it is a loop that runs 100 times (100 statements)...

Scott Pletcher

You can't write a trigger assuming that, because SQL will fire a trigger only ONCE no mattter how many rows are inserted.

The WHERE condition will only do the INSERT/UPDATE when the row(s) match, so that should be enough to ensure good performance, unless there are several conditions you need to check.

Scott Pletcher

The less efficiency only comes when they do actually have to retrieve the Optional1 value. If the WHERE conditions aren't met, you won't have any additional overhead anyway :-) .

webressurs

ASKER

Thank you so much, I really appreciate :)