• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Performance Tuning DB Trigger

Experts,
I have inherited a database that is IO bound on the sql server process.  I'm no sql expert so I'm looking for some help.

Attached are the table schema and triggers (for one table).  I'm looking for some quick fixes, if possible, that would improve the performance(code changes, indexes etc).

Thanks

 sfOpportunity.sql sfService-Order--c.sql trigger-sfOpportunity.sql trigger-2-sfOpportunity.sql
0
mwochnick
Asked:
mwochnick
  • 4
  • 2
  • 2
5 Solutions
 
LowfatspreadCommented:
looks like you are missing at least 1 index

and the triggers should be re-written to process more efficiently

initial thoughts

create index [dbo].[ix_sfService_Order__c_sfid]
 on [dbo].[sfService_Order__c]
 as (sfid asc)


does table sfAccount have an index on sfId  already?

what are the indexes available on

sfCertified_Mail__c
and sfProperty_Owner_Document_Fili

tables ?

any reason for having two triggers as opposed to just 1?

any reason that the triggers arn't written to properly deal with
all types of update scenario?
(i.e. multiple row update is not catered for)
0
 
dqmqCommented:
Recommend index on [sfId] column in service order table.

--------------------------------------------------
Also, those triggers are potentially disasterous.  They are written as ROW-level triggers, but SQL Server only has STATEMENT-level triggers.  Please learn how STATEMENT level triggers work when multiple rows are updated at the same time.  (And please do not insist that it will never happen. At a minimum, please add code to abort the transaction if the .inserted table contains more than one row).
0
 
LowfatspreadCommented:
have you posted all of the second trigger it talks about fixing the closedate however that code doesn't seem to be present...

certainly the triggers appear to be called unnecessarily given that the first performs an update to the original table...

so consolidating to a single trigger would be beneficial.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LowfatspreadCommented:
replace both the current triggers with this 1 perhaps?
create TRIGGER [dbo].[maintain_sfOpportunity]
   ON  [dbo].[sfOpportunity]
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

UPDATE sfOpportunity
   SET do_sync_flag = 'S',
       sfProject__c = I.ServOrdProject,
       sfPDS__DataSynch_Modified_Date = NULL
  from sfOpportunity as sfo
 Inner Join (
SELECT IsNull(i.sfService_Order__c,'C') as OppServOrdID 
      , I.sfStageName,  I.sync_id 
      , IsNull(sfStage__c,'Closed') as SOStage
     , IsNull(SO.sfPM_C_ID__c,'A') as PMCIDPID 
     , IsNull(SO.sfProject__c,'A') as ServOrdProject
     , IsNull(i.sfProject__c,'B') as OppProject
  FROM inserted I 
 INNER JOIN sfAccount A 
    ON i.sfAccountId = A.sfId
  LEFT OUTER JOIN sfService_Order__c SO 
    ON i.sfService_Order__c = SO.sfID
    ) as I
on sfo.sync_id = I.SYNC_ID
Where (sfo.do_sync_flag <> 's'
   or sfo.sfproject__c <> I.servordproject
   or sfo.sfPDS__DataSynch_Modified_Date is not null
       )
 and IsNumeric(i.PM_CID_PID) = 1 
 AND i.ServOrdProject <> 'A' 
 AND (i.OppProject = 'B'
      or i.OppProject <> i.ServOrdProject)


UPDATE sfService_Order__c
   SET sfStage__c = i.sfStageName
     , do_sync_flag = 'S'
  FROM sfService_Order__c 
 INNER JOIN (
SELECT IsNull(i.sfService_Order__c,'C') as OppServOrdID 
      , I.sfStageName,  I.sync_id 
      , IsNull(sfStage__c,'Closed') as SOStage
     , IsNull(SO.sfPM_C_ID__c,'A') as PMCIDPID 
     , IsNull(SO.sfProject__c,'A') as ServOrdProject
     , IsNull(i.sfProject__c,'B') as OppProject
  FROM inserted I 
 INNER JOIN sfAccount A 
    ON i.sfAccountId = A.sfId
  LEFT OUTER JOIN sfService_Order__c SO 
    ON i.sfService_Order__c = SO.sfID
    ) as I
    ON sfService_Order__c.sfId = i.OppServOrdID
where (do_sync_flag <> 's'
          or sfStage__c <> i.sfstagename)
and IsNumeric(i.PM_CID_PID) = 1 
AND SUBSTRING(i.sfStageName,1,4) = 'Bill' 
AND i.OppServOrdID <> 'C' 
AND SUBSTRING(i.SOStage,1,5) <> 'Close'


-- If an Opportunity changes service order or project
-- have the CertMail and the ProjPropOwn 
-- Document Filing keep up to date

UPDATE sfCertified_Mail__c
  SET do_sync_flag = 'S'
from sfCertified_Mail__c as M
Inner join (
SELECT i.sfId 
,  IsNull(i.sfService_Order__c,'B') as INS_SrvOrdID 
,  IsNull(i.sfProject__c,'B') as INS_Proj_ID 
,  case when d.sfid is null then null
        else IsNull(sfService_Order__c,'A') 
        end as DEL_SrvOrdID
, case when d.sfid is null then null
       else IsNull(sfProject__c,'A') 
       end as DEL_Proj_ID
FROM inserted as I
left outer join deleted as d
on i.sfid=d.sfid
) as I
on m.sfOpportunity__c = i.sfId
WHERE do_sync_flag not in ( 'D','S')
and (   (i.INS_SrvOrdID <> i.DEL_SrvOrdID AND i.DEL_SrvOrdID <> 'A')
     OR (i.INS_Proj_ID <> i.DEL_Proj_ID AND i.DEL_Proj_ID <> 'A') 
    )



UPDATE sfProperty_Owner_Document_Fili
   SET do_sync_flag = 'S'
  FROM sfProperty_Owner_Document_Fili as F
 Inner join (
SELECT i.sfId 
,  IsNull(i.sfService_Order__c,'B') as INS_SrvOrdID 
,  IsNull(i.sfProject__c,'B') as INS_Proj_ID 
,  case when d.sfid is null then null
        else IsNull(sfService_Order__c,'A') 
        end as DEL_SrvOrdID
, case when d.sfid is null then null
       else IsNull(sfProject__c,'A') 
       end as DEL_Proj_ID
FROM inserted as I
left outer join deleted as d
on i.sfid=d.sfid
) as I
 on f.sfService_Order__c = i.INS_SrvOrdID 
 where do_sync_flag not in ( 'D','S')
   and i.INS_Proj_ID <> i.DEL_Proj_ID 
   AND i.DEL_Proj_ID <> 'A' 

END

Open in new window

0
 
mwochnickAuthor Commented:
Lowfatspread:

the code that refers to the closedate was commented out - so I removed it for clarity

dqmq:
do you know of a good reference/example on how to code to the STATEMENT level as opposed to row level?
0
 
LowfatspreadCommented:
"statement" level

just look at the examples in msdn/bol

as with all sql you "should" normally be trying to process a set of results rather than individual rows..

if you do need to process individual rows then use a loop/cursor to perform that (but still try to process in "sets")

the basic problem with your existing triggers is that they are naively written with the assumption that only a single row will
have been affected by the originating statement... That is a typical developer mistake , and why i would always recommend that triggers are written/actually validated by a dba. (along with the general performance requirements which need to be considered).

0
 
dqmqCommented:
>do you know of a good reference/example on how to code to the STATEMENT level as opposed to row level?

Unbelievably, I cannot find a good SQL Server example on the web.  The Oracle community has some good examples; perhaps try a search on that.

Briefly, I can tell you a little. In SQL Server, the trigger only fires once regardless of how many rows are updated.   The .inserted and .deleted tables contain rows for each row that is affected by the update statement.   When your trigger does something like this:

Select @newval = somecol from inserted
Select @oldval = somecol from inserted

Then you are retriving an old value from just one of the updated rows and a new value from one of the updated rows.  The old value an the new value may not even come from the same row.

Basically, there are two design issues that pertain to statement-level triggers.  
1.  You need logic to apply the trigger multiple times:  once for each row updated.  Typically, that is done with an SQL statement that joins to the .inserted/.deleted tables and uses set operations to affect multiple rows.  Alternatively, you can cursor over the .inserted/.deleted tables and use row operateions to affect multiple rows.

2. When comparing .inserted values to .deleted values, you need to make sure they are coming from the same row.  Typically, that's accomplished by joining those to psuedo tables on the primary key.




   




0
 
mwochnickAuthor Commented:
Thanks much appreciated
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now