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

• SQL_Easy_btn?

  SSC Eights!

  Points: 820

  More actions

  February 12, 2009 at 3:00 pm

  #943222

  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: 1004494

  More actions

  February 13, 2009 at 2:44 am

  #943410

  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: 18484

  More actions

  February 13, 2009 at 3:46 am

  #943427

  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

  February 16, 2009 at 6:23 am

  #944471

  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

  end

• spaghettidba

  SSC Guru

  Points: 105712

  More actions

  February 16, 2009 at 6:40 am

  #944482

  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

  END

  Regards

  Gianluca

  -- Gianluca Sartori

• Lowell

  SSC Guru

  Points: 323482

  More actions

  February 16, 2009 at 6:54 am

  #944491

  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

  February 16, 2009 at 6:59 am

  #944494

  The whole table does not have to be updated everytime.

• spaghettidba

  SSC Guru

  Points: 105712

  More actions

  February 16, 2009 at 7:03 am

  #944497

  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

  February 16, 2009 at 7:12 am

  #944502

  ok. I will try it.

  Thanks everyone for your responses.

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  February 16, 2009 at 7:24 am

  #944511

  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

  February 16, 2009 at 7:54 am

  #944546

  Thanks, it is working fine now.