Help with trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with trigger

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 13:45:11

Hi, My problem is this..Please spare some time to read thru. Sorry about the length.

I have a couple of UPDATE statements (both given below) that run as part of a SP. The first update , affects about 500 rows and the second update affects around 400,000 rows.

--Update the INTERFACE_IND AS 'R'.
UPDATE PACTG
SET INTERFACE_IND = 'R'
FROM PACTG AS A WITH (NOLOCK),
CACTGDAILY AS C WITH (NOLOCK)
WHERE A.ACTG_KEY0 = C.ACTG_KEY0
AND A.DATE_REVERSED > 0
AND A.REVERSAL_CODE = 'Y'

--Update the INTERFACE_IND AS 'E'.
UPDATE PACTG
SET INTERFACE_IND = 'E'
FROM PACTG AS A WITH (NOLOCK),
CACTGDAILY AS C WITH (NOLOCK)
WHERE A.ACTG_KEY0 = C.ACTG_KEY0
AND A.DATE_REVERSED = 0
AND A.DATE_ADDED > 0
AND A.REVERSAL_CODE <> 'Y'

There is a Trigger on this PACTG table which inturn inserts two records (for each update an 'I' record and 'D' record gets written) into another tracking table called LPUPDATES.

This is the trigger code snippet.

CREATE TRIGGER PACTG_TRIG_UPDATE on PACTG FOR UPDATE
AS
BEGIN TRAN T1
INSERT INTO LPUPDATES (KEY0_STRING,TABLE_NAME,UPDATE_IND, DATE_STAMP) SELECT ACTG_KEY0, 'PACTG','D', GETDATE() FROM Deleted
INSERT INTO LPUPDATES (KEY0_STRING,TABLE_NAME,UPDATE_IND, DATE_STAMP) SELECT ACTG_KEY0, 'PACTG','I', GETDATE() FROM Inserted
COMMIT TRAN T1
GO

When I ran the stored procedure around 12:10 today, it completed at 12:54 PM..which is way more time.

This is what I was able to observe...

For the first update, the records in LPUPDATE got inserted at 12:15 at the following timeframe. All the 'D' records got inserted first at '2009-03-10 12:15:13.403'and the 'I' records got inserted at '2009-03-10 12:15:14.250'.

When the second update ran..(which affects arnd 400,00 rows), there is a remarkable difference between the 'I' record insertion time and 'D' record time.

All 'D' records got inserted at '2009-03-10 12:22:42.323' and 'I' records dint get inserted until '2009-03-10 12:54:03.280'.

Since it is only an UPDATE on PACTG, I would assume both the "I' and 'D" should get inserted at the same time?

Why is this behavior? Can someone please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:51:31

nope. for each batch update the trigger will first get batch of records from DELETED table do insertion and then get records from INSERTED and inserts. thats why all D records have inserted date value less than I records for each batch.
Now each update batch causes seperate firing of trigger. thats why second batches D & I records got inserted after first batch, with I records following D's

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 13:54:34

So..does that mean.. this statement alone takes 12:54 - 12:22 i.e 32 mins?

INSERT INTO LPUPDATES (KEY0_STRING,TABLE_NAME,UPDATE_IND, DATE_STAMP) SELECT ACTG_KEY0, 'PACTG','I', GETDATE() FROM Inserted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 14:14:18

yup.. does your LPUPDATES table have indexes, triggers,...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-10 : 14:33:56

vijay is this the same project with the same crazy indexes and all :O)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119740

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 14:34:37

It doesn't have triggers..it has indexes..

There is a RECORD_ID which is an indentity thats the unique,clusterred index.

and there are 3 non clustered indexes..as below

LPUPDATES_X_1 - PROCESSED_FLAG, TABLE_NAME, UPDATE_IND
LPUPDATES_X_2 - DATE_STAMP
LPUPDATES_X_3 - PROCESSED_FLAG, TABLE_NAME, KEY0_STRING

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 14:35:55

presence of indexes will make inserts slower as index needs also to be updated with new record info.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 14:36:53

Yosisz...actually yes...

. But this is with s different SP.

The changes that we discussed were great..and it brought down execution time to a great extent. This is the final step of the process where we update the Master table PACTG..and this is killing me with time...mostly because of the trigger.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-10 : 15:14:06

what is good for the goose is good for the gander.

your trigger like visakh is pointing is as good as your indexes. Gotta clean up that stuff you inherited..triggers are supposed to be fast and limber...
visakh16 is it not possible to tell the process explicitly not to update indexes, just this time.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 15:50:45

Yeah..if there is a way not to update indexes..and just do the insert..it wud be great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 14:04:54

why dont you drop indexes before your processing and recreate it after?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources