Performance Tuning SQL query in Trigger

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 2000 Forums

Transact-SQL (2000)

Performance Tuning SQL query in Trigger

Author

Topic

iganesh2k2
Starting Member

6 Posts

Posted - 2007-06-19 : 03:33:22

i am using sql server 2000. I have written update trigger on CORP_CAGE table to log the details in

CORP_CAGE_LOG_HIST table,if any changes in EMP_SEQ_NO column.

please find the structure of CORP_CAGE table:

1.CORP_CAGE_SEQ_NO
2.RECEIVED_DATE
3.EMP_SEQ_NO

CORP_CAGE table is having 50,000 records. the trigger "Check_Update" is fired when i am executing the following

query from application which updates 10,000 records.

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

please find below the trigger,in that, trigger can easily find whether any UPDATE done in EMP_SEQ_NO column by using

UPDATE FUNCTION.
But,when it come to insert part, it takes more time(nearly 1 hour or sometimes it will hang.).For minimum

records,this trigger is working fine.

Create trigger Check_Update ON dbo.CORP_CAGE FOR UPDATE AS
BEGIN
IF UPDATE(EMP_SEQ_NO)
BEGIN
INSERT CORP_CAGE_LOG_HIST
(
CAGE_LOG_SEQ_NUM,
BEFORE_VALUE,
AFTER_VALUE,
ENTRY_USER,
FIELD_UPDATED
)
SELECT
i.CAGE_LOG_SEQ_NUM,
d.RECEIVED_DATE,
i.RECEIVED_DATE,
i.UPDATE_USER,
"EMP_SEQ_NO"
FROM
inserted i,
deleted d
WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM
END

END

please help me on this for performance tuning the below query.

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 03:40:01

How long does your server take to insert 10,000 records?

perhaps try:


-- Make temp table:
SELECT *
INTO TEMP_CORP_CAGE_LOG_HIST
FROM CORP_CAGE_LOG_HIST
WHERE 1=1
GO

DECLARE @StartTime DATETIME
SET @StartTime = GetDate()
INSERT CORP_CAGE_LOG_HIST
(
    CAGE_LOG_SEQ_NUM,
    BEFORE_VALUE,
    AFTER_VALUE,
    ENTRY_USER,
    FIELD_UPDATED
)
SELECT TOP 10000
    CAGE_LOG_SEQ_NUM,
    RECEIVED_DATE,
    RECEIVED_DATE,
    UPDATE_USER,
    'EMP_SEQ_NO'
FROM  dbo.CORP_CAGE
--
SELECT [Elapsed (Sec)] = DATEDIFF(Second, @StartTime, GetDate())

-- Don't forget this: !!
-- DROP TABLE TEMP_CORP_CAGE_LOG_HIST

Kristen

iganesh2k2
Starting Member

6 Posts

Posted - 2007-06-19 : 04:47:35

Kristen,
Thanks for ur reply. server was hanging when i was updating 10,000 records.it should finish with in a minute. i think the problem is that i joined inserted and deleted table. give me the suggestion to replace this insert part with new way.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-06-19 : 07:24:30

(Note: thread moved out of the article discusssion forum.)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 08:02:14

"i think the problem is that i joined inserted and deleted table"

I doubt that's the problem.

I'd still like to know how long a straight 10,000 row insert takes. That will tell us the minimum time that your hardware can insert 10,000 rows.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources