SQL Server Performance

Problems with Linked server update/insert

Discussion in 'T-SQL Performance Tuning for Developers' started by MagnusJ, Oct 13, 2003.

  1. MagnusJ New Member

    I have a problem related to performance when making insert/updates to another SQL Server through a Linked server - connection. The Linked server - connection uses the standard server type = SQL Server. When I for example do an INSERT from a company table with 1,3 MB of data (checked with sp_spaceused) to another company table on the other SQL Server, I will transfer around 8MB through for example a VPN or RAS conection, which seams to be a very large overhead and gives poor performance. Does anyone have any tips on what to do ?




    MagnusJ
  2. Argyle New Member

    Check out my reply in this post for some issues I encountered with linked servers:
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=449

    One of them:
    -----------------------------
    Issue 3:
    Extremely slow performance between linked servers. Especially when working with large amount of data.

    Solution:
    Be sure to know how you code your queries. There are many issues to look out for when working with linked servers. Doing selects or updates trough a linked server reference can cause you to fetch the entire table data via slow cursors even if you only want one row. Use the command OPENQUERY if possible.

    Example:
    The following query will use a very slow cursor and fetch data row by row (look for sp_cursoropen in profiler)
    INSERT INTO [myDB].[dbo].[myTable]([myID], [myInfo])
    SELECT * FROM myLinkedServer.myOtherDB.dbo.myOtherTable

    Rewrite it like this and all rows will be sent over in a single batch:
    INSERT INTO [myDB].[dbo].[myTable]([myID], [myInfo])
    SELECT * FROM OPENQUERY(myLinkedServer, 'SELECT * FROM myOtherDB.dbo.myOtherTable')
    -----------------------------

    Monitor with profiler to make sure you don't have a lot of sp_cursorfetch statments. If you do change to a pass-trough query instead like OPENQUERY. This requires more memory but is much much faster.

    /Argyle
  3. satya Moderator

  4. MagnusJ New Member

    I'm most certain that this answers will help me,
    I shall true this as soon as possible, thank You.

    MagnusJ
  5. MagnusJ New Member

    Your suggestion was
    INSERT INTO [myDB].[dbo].[myTable]([myID], [myInfo])
    SELECT * FROM myLinkedServer.myOtherDB.dbo.myOtherTable
    Rewrite it like this and all rows will be sent over in a single batch:
    INSERT INTO [myDB].[dbo].[myTable]([myID], [myInfo])
    SELECT * FROM OPENQUERY(myLinkedServer, 'SELECT * FROM myOtherDB.dbo.myOtherTable')

    This works well and improves performance if i Do it from the server in which i would like to do the inserts/updates, but how should I do it when i'm developing a centralised solution based on dynamic SQL, where I would like the central server to initiate and perform the insert/updates on the linked servers (actually a number of mobile clients) instead of the other way around. I could for example create a stored procedure om the remote computers which i could call and submit the insert- and select-statements created by dynamic SQL on the central server and then perfrom it from the remote server, but this seems like a complicated way to do it.
    Do You have an example of how I could do this directly using the OPENQUERY function ?



    MagnusJ
  6. satya Moderator

    Check books online for OPENQUERY topic for any pointers.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. MagnusJ New Member

    I noticed know that that the OPENQUERY function does not accept
    variables for its arguments. I assumes that means that it is impossible
    for me to use OPENQUERY or OPENROWSET function together since the solution
    is based on insert/update/select - statements created with dynamic SQL
    (executed with sp_executesql function) ?
    Is there any other technique available that can accept variables
    (and still improve performance...) ?

    MagnusJ
  8. satya Moderator

    Or other way is to use SPs and call to execute from other server.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. MagnusJ New Member

    Seems like I'm back on page 1 again (so to speak..), this is an example
    of how it´s working today.
    So as I understand it , there is´nt so much more I can do according to
    T-SQL code issues ?
    (except from index tuning, create statistics and other "normal issues"?

    -- Example of code executed from central server for update in Linked server
    declare @SqlUpdateStment nvarchar(4000)

    -- In between is a number of steps to build up the @SqlUpdateStment dynamically
    -- according to for example client information, tables and column definitions/info
    -- which is not shown here but the result could be something like this

    select @SqlUpdateStment = 'update [MyLinkedServer].MyLinkedDB.dbo.xktx
    set [MyLinkedServer].MyLinkedDB.dbo.xktx.edit = xktx.edit
    ,[MyLinkedServer].MyLinkedDB.dbo.xktx.ForetagKod = xktx.ForetagKod
    ,[MyLinkedServer].MyLinkedDB.dbo.xktx.ftgnr = xktx.ftgnr
    ,[MyLinkedServer].MyLinkedDB.dbo.xktx.kodfasttext = xktx.kodfasttext
    ,[MyLinkedServer].MyLinkedDB.dbo.xktx.kodfasttext = xktx.kodfasttext
    ,[MyLinkedServer].MyLinkedDB.dbo.xktx.rowcreatedby = xktx.rowcreatedby
    ,[MyLinkedServer].MyLinkedDB.dbo.xktx.rowcreateddt = xktx.rowcreateddt
    ,[MyLinkedServer].MyLinkedDB.dbo.xktx.rowupdatedby = xktx.rowupdatedby
    [MyLinkedServer].MyLinkedDB.dbo.xktx.rowupdateddt = xktx.rowupdateddt
    from [MyLinkedServer].MyLinkedDB.dbo.xktx t1, xktx
    where xktx.rowupdateddt >= ''''2003-10-23 19:02:00''''
    and xktx.rowcreateddt < ''''2003-10-23 19:02:00''''
    and t1.ftgnr=xktx.ftgnr
    and t1.kundtextkod=xktx.kundtextkod'

    set @SqlUpdateStment=@SqlUpdateStment + ' set @MyError= @@ERROR'

    -- Execute UpdateStatement
    Execute sp_executesql @SqlUpdateStment,N'@MyError int output',@MyError = @Error output

    MagnusJ
  10. Argyle New Member

  11. MagnusJ New Member

    The solution i´m working on executes all this tasks, like INSERT, UPDATE and DELETE
    (not as often) , but it is between SQL 2000 Servers (sp3). The UPDATE example is
    roughly the same principle as when I execute INSERT-statements on a linked server.

    So I'm afraid my question remains:
    Is there any better technique when using serversided stored procedures (since I can´t use OPENQUERY or OPENROWSET) to perform this matters when I need to use dynamic SQL and want to have a centralized solution where most of the code created and executed on the central server ?

    MagnusJ
  12. Twan New Member

    Hi ya,

    Is it an option to create views which span across servers, and update/insert/delete from those?

    so view to [MyLinkedServer].MyLinkedDB.dbo.xktx called v_server_xktx

    select @SqlUpdateStment = 'update t1
    set edit = xktx.edit
    ,ForetagKod = xktx.ForetagKod
    ,ftgnr = xktx.ftgnr
    ,kodfasttext = xktx.kodfasttext
    ,kodfasttext = xktx.kodfasttext
    ,rowcreatedby = xktx.rowcreatedby
    ,rowcreateddt = xktx.rowcreateddt
    ,rowupdatedby = xktx.rowupdatedby
    ,rowupdateddt = xktx.rowupdateddt
    from v_server_xktx t1, xktx
    where xktx.rowupdateddt >= ''''2003-10-23 19:02:00''''
    and xktx.rowcreateddt < ''''2003-10-23 19:02:00''''
    and t1.ftgnr=xktx.ftgnr
    and t1.kundtextkod=xktx.kundtextkod'


    Something I just noticed while writing this up
    - you don't need to include the remote table twice. In your example you are calling the table by its proper name in the update and then joining it again in the from part. Not sure how efficient that will be... and for readability the columns to the left of the = in the set clause don't need a table name

    try changing the update to


    select @SqlUpdateStment = 'update t1
    set edit = xktx.edit
    ,ForetagKod = xktx.ForetagKod
    ,ftgnr = xktx.ftgnr
    ,kodfasttext = xktx.kodfasttext
    ,kodfasttext = xktx.kodfasttext
    ,rowcreatedby = xktx.rowcreatedby
    ,rowcreateddt = xktx.rowcreateddt
    ,rowupdatedby = xktx.rowupdatedby
    rowupdateddt = xktx.rowupdateddt
    from [MyLinkedServer].MyLinkedDB.dbo.xktx t1, xktx
    where xktx.rowupdateddt >= ''''2003-10-23 19:02:00''''
    and xktx.rowcreateddt < ''''2003-10-23 19:02:00''''
    and t1.ftgnr=xktx.ftgnr
    and t1.kundtextkod=xktx.kundtextkod'

    Cheers
    Twan

Share This Page