Solved

Performance Tuning DB Trigger

Posted on 2011-09-25
8
182 Views
Last Modified: 2012-05-12
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
Comment
Question by:mwochnick
  • 4
  • 2
  • 2
8 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 300 total points
ID: 36595392
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
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 200 total points
ID: 36595430
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36595603
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 300 total points
ID: 36595660
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 12

Author Comment

by:mwochnick
ID: 36595759
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 300 total points
ID: 36595951
"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
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 200 total points
ID: 36595962
>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
 
LVL 12

Author Closing Comment

by:mwochnick
ID: 36596147
Thanks much appreciated
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now