Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: Set XACT_ABORT ON

    Newbie question
    I have an Update trigger and an Insert trigger on a table both of which use a variant of the code below. The trigger takes information from the table and updates information in another database configured as a linked server.


    The update trigger works fine without the Set XACT_ABORT ON statement.
    The insert trigger crashes unless I put this line in.

    The update trigger runs 10 times faster without the statement.
    Is there an alternative to this that will allow the insert trigger to run without crashing? and run faster. The insert trigger takes forever to run now. (Forever being about 20 seconds). The Update trigger takes about 2 seconds without the Set XACT_ABORT ON statement and 20 seconds with it.

    Any help would be appreciated.
    Tom



    Code:
    Set XACT_ABORT ON
    
    Select @pos=COUNT(*)	From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
    where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid2)) and GCC_CONTACT_SEQ=@maxseq2
                
        
    
    if(@pos>0)
    	begin
    	Update SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL  
     	set GCC_BUSINESS_UNIT=UPPER(@tmpbus),GCC_CONTACT_NAME=isnull(@nameout,' '),
    	GCC_CONTACT_ID=@tmpcompid,GCC_CONTACT_SEQ=@maxseq,
    	GCC_CONTACT_CMPY=isnull(UPPER(@compout),' '),GCC_CONTACT_ADDR1=isnull(@add1out,' '),
    	GCC_CONTACT_ADDR2=isnull(@add2out,' '),GCC_CONTACT_ADDR3=isnull(@add3out,' '),
    
    	GCC_CONTACT_CITY=isnull(@tmpcity,' '),GCC_CONTACT_STATE=isnull(UPPER(@tmpstate),' '),
    	GCC_CONTACT_ZIP=isnull(UPPER(@zipout),' '),GCC_CONTACT_CNTRY=isnull(UPPER(@ctryout),' '),
    	GCC_CONTACT_PHONE=isnull(UPPER(@tel1out),' '),GCC_CONTACT_FAX=isnull(UPPER(@tel2out),' '),GCC_CONTACT_CELL=isnull(UPPER(@tel3out),' '),
    	GCC_CONTACT_EMAIL=isnull(@emailout,' '),GCC_EFF_STATUS=@outstatus,GCC_OPRID=isnull(@mgrout,' '),GCC_DTTM_STAMP=CONVERT(Char, GetDate(), 101)
    	where UPPER(RTRIM(@tmpcompid2))=UPPER(GCC_CONTACT_ID) and @maxseq2=GCC_CONTACT_SEQ  
    
    
    	end
    else	
    	begin
    	Select @rcount=count(GCC_CONTACT_SEQ)
    	From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
    	where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid)) AND GCC_CONTACT_SEQ=@maxseq AND UPPER(RTRIM(@nameout))=UPPER(RTRIM(GCC_CONTACT_NAME)) 
    
    	
                
    
        if(@rcount<1)
    		begin
    
    		Insert into SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL 
    		(GCC_BUSINESS_UNIT,GCC_CONTACT_ID,GCC_CONTACT_SEQ,GCC_EFFDT,GCC_CONTACT_NAME,GCC_CONTACT_CMPY,GCC_CONTACT_TYPE,GCC_CONTACT_ADDR1,GCC_CONTACT_ADDR2,GCC_CONTACT_ADDR3,GCC_CONTACT_ADDR4,GCC_CONTACT_CITY,GCC_CONTACT_CNTY,GCC_CONTACT_STATE,GCC_CONTACT_ZIP,GCC_CONTACT_CNTRY,GCC_CONTACT_PHONE,GCC_CONTACT_FAX,GCC_CONTACT_CELL, GCC_CONTACT_EMAIL,GCC_EFF_STATUS,GCC_OPRID,GCC_DTTM_STAMP) 
    		values(UPPER(@tmpbus),UPPER(@tmpcompid),@maxseq,CONVERT(Char, GetDate(), 101),isnull(@nameout,' '),
            isnull(UPPER(@compout),' '),'E',isnull(@add1out,' '),isnull(@add2out,' '),isnull(@add3out,' '),'  ',isnull(@tmpcity,' '),'  ',
            isnull(UPPER(@tmpstate),' '),isnull(UPPER(@zipout),' '),isnull(UPPER(@ctryout),' '),isnull(UPPER(@tel1out),' '),isnull(UPPER(@tel2out),' '),isnull(UPPER(@tel3out),' '),
            isnull(@emailout,' '),@outstatus,isnull(@mgrout,' '),CONVERT(Char, GetDate(), 101))
    		end
    
    	end
    
                 
    end

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The reason for slow performance lies in expression-based comparison in your WHERE clauses. I have no explanation on why INSERT trigger runs much slower than the UPDATE one, because there is not much you gave us to go about comparing them. XACT_ABORT setting is actually required for distributed transactions, so this needs to be implemented in both triggers. Also, I would recommend replacing direct action queries against the linked server with calls to stored procedures residing on the it. This will guarantee that the local optimizer will be involved in processing your UPDATEs/INSERTs.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •