Performace issue while updating records and trigger on table

Question

Performace issue while updating records and trigger on table

nil.hajare

Valued Member

Points: 64
More actions
June 4, 2013 at 10:57 pm

#273503

Hi All,
I am having a performance issue while updating records into sql server table,
I have created below trigger to update related tables if the status of this table is updated/Changed.
USE [EMEA_SERVICEMAX]
GO
/****** Object: Trigger [Servicemax].[ERP_ACCOUNT_DATE_UPDATE] Script Date: 06/05/2013 10:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Servicemax].[ERP_ACCOUNT_DATE_UPDATE]
ON [EMEA_SERVICEMAX].[Servicemax].[ERP_ACCOUNT_STG]
FOR UPDATE
AS BEGIN
DECLARE @nOldValue varchar(20),@nNewValue varchar(20),@record_dt date,@CUST_NUM varchar(20),
@SOURCE_SERVER varchar(15),@COMPANY_NO int
SELECT @nOldValue=b.CUSTOMER_STATUS, @nNewValue=a.CUSTOMER_STATUS ,@record_dt=a.record_insert_dt
,@CUST_NUM=a.customer_number,@SOURCE_SERVER = a.SOURCE_SERVER,@COMPANY_NO=a.COMPANY_NO
FROM inserted a, deleted b
IF @nNewValue = @nOldValue
return
else
BEGIN
update EMEA_SERVICEMAX.Servicemax.SALES_ORDERS_STG
set record_insert_dt=@record_dt
where customer_number=@CUST_NUM
and SOURCE_SERVER=@SOURCE_SERVER
and COMPANY_NO=@COMPANY_NO
update EMEA_SERVICEMAX.Servicemax.INSTALLATION_STG
set record_insert_dt=@record_dt
where LOCATION_CUSTOMER_NO=@CUST_NUM
and SOURCE_SERVER=@SOURCE_SERVER
and COMPANY_NO=@COMPANY_NO
update EMEA_SERVICEMAX.Servicemax.ERP_ACCOUNT_PROJECTS_STG
set record_insert_dt=@record_dt
where customer_number=@CUST_NUM
and SOURCE_SERVER=@SOURCE_SERVER
and COMPANY_NO=@COMPANY_NO
update EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG
set record_insert_dt=@record_dt
where customer_number=@CUST_NUM
and SOURCE_SERVER=@SOURCE_SERVER
and COMPANY_NO=@COMPANY_NO
update EMEA_SERVICEMAX.Servicemax.SERVICE_ORDER_STG
set record_insert_dt=@record_dt
where customer_number=@CUST_NUM
and SOURCE_SERVER=@SOURCE_SERVER
and COMPANY_NO=@COMPANY_NO
END
END
GO
Any help on this may be appreciated.
Thanks,
Nilesh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'd suggest that the performance problem is a secondary concern. That trigger will only work correctly if a single row is updated. Any time there's more than one row changed, the trigger will not do what's intended

nil.hajare (6/4/2013)
SELECT @nOldValue=b.CUSTOMER_STATUS, @nNewValue=a.CUSTOMER_STATUS ,@record_dt=a.record_insert_dt
,@CUST_NUM=a.customer_number,@SOURCE_SERVER = a.SOURCE_SERVER,@COMPANY_NO=a.COMPANY_NO
FROM inserted a, deleted b

Scrap that, rewrite the trigger to handle any number of rows in the inserted/deleted tables. If you still have a performance problem after that, post execution plans.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nil.hajare Valued Member Points: 64 More actions · Answer 2

Thanks,

I am new on sql server. Any suggestions would be helpful for me to rewrite this trigger.

Could you please help me on this?

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 3

Without knowing DDL and having sample data to test against, this is a W.A.G. but maybe it'll help point you in the correct direction.

UPDATE stg

SET Record_insert_dt = a.record_insert_dt

FROM inserted a

INNER JOIN EMEA_SERVICEMAX.Servicemax.SALES_ORDERS_STG stg

ON a.Customer_Number = stg.Customer_Number

AND a.Source_Server = stg.SourceServer

AND a.Company_No = stg.Company_No;

The question is why are you trying to update multiple tables with one trigger?

And why are you doing an inner join on deleted from inserted? (EDIT: I have an idea, but I want to hear it from you.)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lowell SSC Guru Points: 323518 More actions · Answer 4

it's going to look a lot like this:

the only thing i could not determine was the primary key that woudl related the INSERTED to the DELETED ; your example did not make that clear so far:

CREATE TRIGGER [Servicemax].[ERP_ACCOUNT_DATE_UPDATE]

ON [EMEA_SERVICEMAX].[Servicemax].[ERP_ACCOUNT_STG]

FOR UPDATE

AS

BEGIN

--SALES_ORDERS_STG

UPDATE MyTarget

SET MyTarget.record_insert_dt = INSERTED.record_insert_dt

FROM EMEA_SERVICEMAX.Servicemax.SALES_ORDERS_STG MyTarget

INNER JOIN INSERTED

ON MyTarget.customer_number = INSERTED.customer_number

AND MyTarget.SOURCE_SERVER = INSERTED.SOURCE_SERVER

AND MyTarget.COMPANY_NO = INSERTED.COMPANY_NO

INNER JOIN DELETED ON INSERTED.PRIMARYKEY = DELETED.PRIMARYKEY

WHERE INSERTED.CUSTOMER_STATUS <> DELETED.CUSTOMER_STATUS

--INSTALLATION_STG

UPDATE MyTarget

SET MyTarget.record_insert_dt = INSERTED.record_insert_dt

FROM EMEA_SERVICEMAX.Servicemax.INSTALLATION_STG MyTarget

INNER JOIN INSERTED

ON MyTarget.customer_number = INSERTED.customer_number

AND MyTarget.SOURCE_SERVER = INSERTED.SOURCE_SERVER

AND MyTarget.COMPANY_NO = INSERTED.COMPANY_NO

INNER JOIN DELETED ON INSERTED.PRIMARYKEY = DELETED.PRIMARYKEY

WHERE INSERTED.CUSTOMER_STATUS <> DELETED.CUSTOMER_STATUS

--ERP_ACCOUNT_PROJECTS_STG

UPDATE MyTarget

SET MyTarget.record_insert_dt = INSERTED.record_insert_dt

FROM EMEA_SERVICEMAX.Servicemax.ERP_ACCOUNT_PROJECTS_STG MyTarget

INNER JOIN INSERTED

ON MyTarget.customer_number = INSERTED.customer_number

AND MyTarget.SOURCE_SERVER = INSERTED.SOURCE_SERVER

AND MyTarget.COMPANY_NO = INSERTED.COMPANY_NO

INNER JOIN DELETED ON INSERTED.PRIMARYKEY = DELETED.PRIMARYKEY

WHERE INSERTED.CUSTOMER_STATUS <> DELETED.CUSTOMER_STATUS

--SERVICE_CONTRACTS_STG

UPDATE MyTarget

SET MyTarget.record_insert_dt = INSERTED.record_insert_dt

FROM EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STG MyTarget

INNER JOIN INSERTED

ON MyTarget.customer_number = INSERTED.customer_number

AND MyTarget.SOURCE_SERVER = INSERTED.SOURCE_SERVER

AND MyTarget.COMPANY_NO = INSERTED.COMPANY_NO

INNER JOIN DELETED ON INSERTED.PRIMARYKEY = DELETED.PRIMARYKEY

WHERE INSERTED.CUSTOMER_STATUS <> DELETED.CUSTOMER_STATUS

--SERVICE_ORDER_STG

UPDATE MyTarget

SET MyTarget.record_insert_dt = INSERTED.record_insert_dt

FROM EMEA_SERVICEMAX.Servicemax.SERVICE_ORDER_STG MyTarget

INNER JOIN INSERTED

ON MyTarget.customer_number = INSERTED.customer_number

AND MyTarget.SOURCE_SERVER = INSERTED.SOURCE_SERVER

AND MyTarget.COMPANY_NO = INSERTED.COMPANY_NO

INNER JOIN DELETED ON INSERTED.PRIMARYKEY = DELETED.PRIMARYKEY

WHERE INSERTED.CUSTOMER_STATUS <> DELETED.CUSTOMER_STATUS

END

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Lowell (6/7/2013)
it's going to look a lot like this:
the only thing i could not determine was the primary key that woudl related the INSERTED to the DELETED ; your example did not make that clear so far:

THAT is the actual performance problem. The lack of criteria between the virtual trigger tables makes a cross join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

nil.hajare (6/7/2013)
Thanks,
I am new on sql server. Any suggestions would be helpful for me to rewrite this trigger.
Could you please help me on this?

Yes. Before you take anyone's word for how to write a trigger, look up how to create them in Books Online (the free help system that comes up when you press the {f1} key.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Not 100% on point but definitely useful for your developement.

https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/