Trigger

Question

Post reply

Trigger

nwinningham

SSC-Addicted

Points: 405
More actions
February 12, 2009 at 2:33 pm

#205510

Any ideas on how I can make this trigger more efficient. I was looking at my one of my reports "object execution statistics" and the numbers for this trigger is extremely high.
ALTER TRIGGER [dbo].[CC_TR_CANDIDATE_PF]
ON [dbo].[CANDIDATE]
FOR UPDATE
AS
begin
UPDATE candidate
SET UDEF_1A_4 =
CASE
WHEN CANDIDATE.CUR_STAGE IN ('DAC','DPR','DRJ','DCO','DPIF','DWC','DWI','DMS','DAPT','DAP','DWA','DPA','DPIJ','DAPJ','DACT','DNI','DPI')
THEN 'D'
WHEN CANDIDATE.CUR_STAGE IN ('EAC','EPR','EWNI','ECO','EPI','EWC','EWI','EMS','EWNA','EAP','EWA','ERJ')
THEN 'E'
WHEN (CANDIDATE.CUR_STAGE IN ('GAC','GMS','GAP','GCO','GWI','GPI','GWC', 'GPR', 'GWA', 'GWNA', 'GRJ', 'GPR','GAZ','GWNI')
and CANDIDATE.CUR_PROG = 'HBMA')
THEN 'H'
WHEN (CANDIDATE.CUR_STAGE IN ('GAC','GMS','GAP','GCO','GWI','GPI','GWC', 'GPR', 'GWA', 'GWNA', 'GRJ', 'GPR','GAZ','GWNI')
and CANDIDATE.CUR_PROG = 'DLMED')
THEN 'D'
END
FROM candidate
WHERE CUR_STAGE IN ('DPA','DWA','DAC','DPR','DRJ','DCO','DPIF','DWC','DWI','DMS','DAPT','DAP','DPIJ','ERJ','EAC','EPR','EWNI','ECO','EPI','EWC','EWI','EMS','EWNA','EAP','EWA',
'GAC','GMS','GAP','GCO','GWI','GPI','GWC', 'GPR', 'GWA', 'GWNA', 'GRJ', 'GPR')
end

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQL_Easy_btn? SSC Eights! Points: 930 More actions · Answer 1

Why are you doing two validations against CUR_STAGE?

You have already validated that they are 'IN' those particular definitions.

I would get rid of the WHERE clause.

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

SQL_Easy_btn? (2/12/2009)
I would get rid of the WHERE clause.

I wouldn't.

The case has no else clause. That means any value that doesn't match any of the case conditions returns NULL. CASE is not a filter. It's an expression that will be applied to all rows in the resultset.

If you remove the where clause, it will update the entire table (which could be far more rows and hence far slower) and those rows that have a curr_stage that's not listed in any of the case conditions will be updated to NULL.

nwinningham: Is there an index on Cur_Stage?

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

Ken McKelvey SSCoach Points: 18856 More actions · Answer 3

I suspect you should be doing something with the inserted pseudo-table.

If the primary key on CANDIDATE is candidateId then something like:

SET QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

ALTER TRIGGER dbo.CC_TR_CANDIDATE_PF

ON dbo.CANDIDATE

AFTER UPDATE

AS

BEGIN

&nbsp&nbsp&nbsp&nbspSET NOCOUNT ON

&nbsp&nbsp&nbsp&nbspUPDATE C

&nbsp&nbsp&nbsp&nbspSET UDEF_1A_4 = D.UDEF_1A_4

&nbsp&nbsp&nbsp&nbspFROM dbo.CANDIDATE C

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspJOIN

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT I.candidateId

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CASE

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN I.CUR_STAGE IN ('DAC','DPR','DRJ','DCO','DPIF','DWC','DWI','DMS','DAPT','DAP','DWA','DPA','DPIJ','DAPJ','DACT','DNI','DPI')

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN 'D'

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN I.CUR_STAGE IN ('EAC','EPR','EWNI','ECO','EPI','EWC','EWI','EMS','EWNA','EAP','EWA','ERJ')

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN 'E'

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN (I.CUR_STAGE IN ('GAC','GMS','GAP','GCO','GWI','GPI','GWC', 'GPR', 'GWA', 'GWNA', 'GRJ', 'GPR','GAZ','GWNI')

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND I.CUR_PROG = 'HBMA')

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN 'H'

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN (I.CUR_STAGE IN ('GAC','GMS','GAP','GCO','GWI','GPI','GWC', 'GPR', 'GWA', 'GWNA', 'GRJ', 'GPR','GAZ','GWNI')

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND I.CUR_PROG = 'DLMED')

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN 'D'

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEND AS UDEF_1A_4

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM inserted I

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp) D

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON C.candidateId = D.candidateId

&nbsp&nbsp&nbsp&nbspWHERE D.UDEF_1A_4 IS NOT NULL

END

GO

nwinningham SSC-Addicted Points: 405 More actions · Answer 4

I also created a table:

if exists (select 1 from sysobjects where type = 'U' and name = 'CCSC_STAGE_UDEF')

drop table CCSC_STAGE_UDEF

go

create table CCSC_STAGE_UDEF

(STAGEchar(5)NOT NULL,

PROGchar(5)NOT NULL,

UDEF_VALchar(1)NOT NULL,

constraint PK_CCSC_STAGE_UDEF primary key (STAGE, PROG))

go

insert into CCSC_STAGE_UDEF values ('DAC', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DPR', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DRJ', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DCO', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DPIF', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DWC', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DWI', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DMS', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DAPT', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DAP', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DWA', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DPA', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DPIJ', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DAPJ', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DACT', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DNI', '*', 'D')

insert into CCSC_STAGE_UDEF values ('DPI', '*', 'D')

insert into CCSC_STAGE_UDEF values ('EAC', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EPR', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EWNI', '*', 'E')

insert into CCSC_STAGE_UDEF values ('ECO', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EPI', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EWC', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EWI', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EMS', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EWNA', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EAP', '*', 'E')

insert into CCSC_STAGE_UDEF values ('EWA', '*', 'E')

insert into CCSC_STAGE_UDEF values ('ERJ', '*', 'E')

insert into CCSC_STAGE_UDEF values ('GAC', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GMS', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GAP', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GCO', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GWI', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GPI', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GWC', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GPR', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GWA', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GWNA', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GRJ', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GAZ', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GWNI', 'HBMA', 'H')

insert into CCSC_STAGE_UDEF values ('GAC', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GMS', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GAP', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GCO', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GWI', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GPI', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GWC', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GPR', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GWA', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GWNA', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GRJ', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GAZ', 'DLMED', 'D')

insert into CCSC_STAGE_UDEF values ('GWNI', 'DLMED', 'D')

go

and change my trigger, but the numbers are still high.

ALTER TRIGGER [dbo].[CC_TR_CANDIDATE_PF]

ON [dbo].[CANDIDATE]

FOR UPDATE

AS

begin

if update (CUR_STAGE)

begin

UPDATE candidate

SET candidate.UDEF_1A_4 = lkp.UDEF_VAL

FROM candidate

left outer join CCSC_STAGE_UDEF lkp

on candidate.CUR_STAGE = lkp.STAGE

and (candidate.CUR_PROG = lkp.PROG or

lkp.PROG = '*')

WHERE lkp.STAGE is not null

end

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

I don't see references to the INSERTED table: try adding the join.

ALTER TRIGGER [dbo].[CC_TR_CANDIDATE_PF] ON [dbo].[CANDIDATE]

FOR UPDATE

AS

BEGIN

IF UPDATE(CUR_STAGE)

BEGIN

UPDATE candidate

SET candidate.UDEF_1A_4 = lkp.UDEF_VAL

FROM candidate

INNER JOIN INSERTED AS I

ON I.[PrimaryKey] = candidate.[PrimaryKey]

LEFT OUTER JOIN CCSC_STAGE_UDEF lkp

ON candidate.CUR_STAGE = lkp.STAGE

AND (candidate.CUR_PROG = lkp.PROG OR lkp.PROG = '*')

WHERE lkp.STAGE IS NOT NULL

END

Regards

Gianluca

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 6

it's your business process, so you'd know better than me...do you need to update the whole table every time?

i thought the trigger should only update from the INSERTED table, and not the candidate table...

it seems to me you are updating the entire table every time the trigger fires, instead of just for the rows that were affected from INSERTED.

UPDATE....

FROM INSERTED

WHERE INSERTED.CUR_STAGE IN ('DPA','DWA','DAC','DPR','DRJ','DCO','DPIF','DWC','DWI','DMS','DAPT','DAP','DPIJ','ERJ','EAC','EPR','EWNI','ECO','EPI','EWC','EWI','EMS','EWNA','EAP','EWA',

'GAC','GMS','GAP','GCO','GWI','GPI','GWC', 'GPR', 'GWA', 'GWNA', 'GRJ', 'GPR')

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!

nwinningham SSC-Addicted Points: 405 More actions · Answer 7

The whole table does not have to be updated everytime.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

So try joining INSERTED, or you'll be updating the whole table. You can use the code I put in my previous post replacing [PrimaryKey] with the actual primary key of the table.

Regards

Gianluca

-- Gianluca Sartori

nwinningham SSC-Addicted Points: 405 More actions · Answer 9

ok. I will try it.

Thanks everyone for your responses.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

I agree with the others that joining to INSERTED is important. I just wanted to add also, that this:

nwinningham (2/16/2009)
UPDATE candidate
SET candidate.UDEF_1A_4 = lkp.UDEF_VAL
FROM candidate
left outer join CCSC_STAGE_UDEF lkp
on candidate.CUR_STAGE = lkp.STAGE
and (candidate.CUR_PROG = lkp.PROG or
lkp.PROG = '*')
WHERE lkp.STAGE is not null

is actually an INNER join and could more easily be written as:

UPDATE candidate

SET candidate.UDEF_1A_4 = lkp.UDEF_VAL

FROM candidate

join CCSC_STAGE_UDEF lkp

on candidate.CUR_STAGE = lkp.STAGE

and (candidate.CUR_PROG = lkp.PROG or

lkp.PROG = '*')

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

nwinningham SSC-Addicted Points: 405 More actions · Answer 11

nwinningham

SSC-Addicted

Points: 405

February 16, 2009 at 7:54 am

#944546

Thanks, it is working fine now.