Trigger execution takes lot time on sql2005server instance

  • We are running multiple instances of SQL2005 on different machines. We are experiencing strange problem where we have trigger on box1.instance1.db1 which is inserting and updating data on box2.instance2.db2, box3.instance3.db3. We have observed that trigger execution is taking hell amount of time. More suprisingly, if we update data individually through query analyzer of box1.instance1.db1 on box2.instance2.db2 and box3.instance3.db3 then it is not taking much time.

    Any help in this regard would be highly appreciated.

    Thanks in advance.

  • Can you post the trigger?

    Is the insert fast when done on the server, but slow when done across a linked server?

    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
  • yes it is slow when done across linked server. please find trigger code as below. Also as i mentioned earilier if i execute same DML statement (i.e. in trigger code) through query analyser from box1.instance1.db1 which is updating data on box2.instance2.db2 and box3.instance3db3 then it is fast.

    USE [asite]

    GO

    /****** Object: Trigger [dbo].[Updt_UsersEmail_dmsdb_master] Script Date: 10/17/2008 10:27:50 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [dbo].[Updt_UsersEmail_dmsdb_master] ON [dbo].[tbl_TPDAddress]

    FOR INSERT, UPDATE

    AS

    BEGIN

    SET XACT_ABORT ON

    declare @v_tpdorgid varchar(200)

    declare @v_tpduserid varchar(200)

    declare @v_orgid int

    declare @v_userid int

    declare @v_OwnerType nvarchar(100), @v_email nvarchar(400)

    declare @v_address1 nvarchar(50), @v_city nvarchar(50), @v_postcode nvarchar(50), @country nvarchar(50),@tel_number nvarchar(50)

    declare @v_n_userid bigint

    select @v_tpdorgid = OrgID,

    @v_tpduserid = UserID ,

    @v_OwnerType = OwnerType,

    @v_email = substring(Email,1,50) ,

    @v_address1 = substring(Street,1,50) ,

    @v_city = substring(City,1,50) ,

    @v_postcode = substring(Postalcode,1,50) ,

    @country = substring(CountryOfOrigin,1,50) ,

    @tel_number = substring(Telephone,1,50)

    from inserted

    if @v_OwnerType = 'USER'

    Begin

    select @v_n_userid = u.user_id

    FROM [ASTDB01\DDMASTER].dmsdb_master.dbo.users U

    INNER JOIN [ASTDB01\DDMASTER].dmsdb_master.dbo.organisation O on ( O.org_id = U.org_id )

    where u.tpd_user_id = @v_tpduserid and O.tpd_org_id = @v_tpdorgid

    -- Asite Collab2.1

    UPDATE [ASTDB01\DDMASTER].dmsdb_master.dbo.users SET email = @v_email where user_id = @v_n_userid

    UPDATE [ASTDB03\MISC01].tenderdb.dbo.users SET email = @v_email where user_id = @v_n_userid

    UPDATE [ASTDB04\DDBAA].dmsdb_master.dbo.users SET email = @v_email where user_id = @v_n_userid

    UPDATE [ASTDB04\DDBAA].tenderdb.dbo.users SET email = @v_email where user_id = @v_n_userid

    UPDATE [ASTDB01\DDMASTER].moodle.dbo.mdl_user SET email = @v_email where username = convert(varchar,@v_n_userid)

    End

    if @v_OwnerType = 'TP'

    Begin

    -- Asite Collab2.1

    UPDATE [ASTDB01\DDMASTER].dmsdb_master.dbo.organisation

    SET address1 = @v_address1,

    city = @v_city,

    postcode = @v_postcode,

    country = @country,

    tel_number = @tel_number

    WHERE tpd_org_id = @v_tpdorgid

    -- Collab2.1 BAA

    UPDATE [ASTDB04\DDBAA].dmsdb_master.dbo.organisation

    SET address1 = @v_address1,

    city = @v_city,

    postcode = @v_postcode,

    country = @country,

    tel_number = @tel_number

    WHERE tpd_org_id = @v_tpdorgid

    End

    IF @@ERROR != 0

    begin

    ROLLBACK TRAN

    end

    END

  • This probably has to do with the boxes taking part in the transaction.

    If you start the DML via SSMS on box1, it is only the three boxes box1, box2 and box3

    which are probably situated in the same lan segment with fast connection.

    If you start the DML via a remote client or a webserver behind a firewall,

    management of the transaction might involve the client-box or the webservers box

    and thus take a lot more time to coordinate.

    Trace the difference on transaction handling on the three SQL Server boxes

    between the two call variants. You'll probably see a totally different timing.

    devloping robust and performant databaseapplications with Microsoft SQL-Server

Viewing 4 posts - 1 through 3 (of 3 total)

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