Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

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

0
webressurs
Asked:
webressurs
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
 
Scott PletcherSenior DBACommented:
>> 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
0
 
BanthorCommented:
TRIGGERS == BAD, EVIL, NO
STOREDPROCEDURES  == GOOD, HEAVEN, BLISS
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
COMMENTS == WRONG WRONG WRONG
0
 
webressursAuthor Commented:
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 :)
0
 
Scott PletcherSenior DBACommented:
First, let's take care of the text column issue.  This should work for that, although slightly less efficiently since it will have to lookup everyone separately :-) :



      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
          Ok = 0 AND
          FromNumber = '5100' AND
          SentWithOperator = '4711' AND
          ErrorCode = '1030'
0
 
Scott PletcherSenior DBACommented:
>> 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: <<

Remember, you're processing all rows as one group, so rather than IF/ELSE statements, use WHERE conditions for each UPDATE with conditions, like you've coded it:


CREATE TRIGGER ...
...
AS
SET NOCOUNT ON
...other lines...

INSERT INTO dbo.SMS_Out
                ( fromNumber, Tel, msg, PriceGroup, Operator,
...
WHERE
          Ok = 0 AND
          FromNumber = '5100' AND
          SentWithOperator = '4711' AND
          ErrorCode = '1030'


UPDATE u
SET
    cellphone = i.Tel --, ... = ...
FROM dbo.tblUser u
INNER JOIN inserted i ON
    i.<keycol> = u.<keycol>
WHERE
    i.Ok = 0 AND
    i.FromNumber = '2500' AND
    i.SentWithOperator = '4710' AND
    i.ErrorCode = '1040'

UPDATE u
...
WHERE
    ...

UPDATE ...

GO
0
 
webressursAuthor Commented:
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

0
 
Scott PletcherSenior DBACommented:
But what if they insert 100 rows in one statement, and 50 are OK and 50 are not OK?
0
 
webressursAuthor Commented:
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)...
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Scott PletcherSenior DBACommented:
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 :-) .
0
 
webressursAuthor Commented:
Thank you so much, I really appreciate :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now