SQL Server Performance

Linked Server problem

Discussion in 'T-SQL Performance Tuning for Developers' started by Martin Line, Dec 15, 2003.

  1. Martin Line New Member

    Hi,<br /><br />I'm having a problem with a linked server. I'm trying to replace an existing SQL job which is of the form<br /><br />'insert into &lt;local db&gt; select from &lt;remote db&gt;'<br /><br />with the reverse, called from the other server ie.<br /><br />'insert into &lt;remote db&gt; select from &lt;local db&gt;' <br /><br />In other words, I'm converting a pull of the data into a push. It works fine [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], but takes 9 times longer to complete [<img src='/community/emoticons/emotion-6.gif' alt=':(' />].<br /><br />Looking at the execution plan, the time seems to be taken up on a table spool, although I can't work out which server the spool is being done. Any help would be much appreciated<br /><br />Cheers<br /><br />Martin<br />
  2. Luis Martin Moderator

    SQL and SP?

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. Martin Line New Member

    The existing SQL runs on SVR2 and pulls the data from SVR1;

    insert into TABLE_A
    (COL_A, COL_B, COL_C, COL_D)
    (selectCOL_A, COL_B, COL_C, COL_D

    The new SQL runs on SVR1 and pushes the data to SVR2;

    (COL_A, COL_B, COL_C, COL_D)

    The old SQL copies 461,000 rows in about a minute, and the new SQL does the same job in 9 minutes.

    I've tried using OPENQUERY, but that has very little effect.


  4. satya Moderator

    In general linked server queries are slow as compared to other.
    If its concerned about insert of records why not take help of DTS or BCP which are faster compared to Linked server.

    Satya SKJ
  5. Martin Line New Member

    I accept this, but why is the 'push' so much slower than the 'pull' ?

    (The actual stored procedure is more complicated, which is why we're using linked servers rather than DTS or BCP. I can cope with a copy time of a minute, but not nine.)
  6. satya Moderator

    Ensure both the SQL server are in same service pack and hotfixes.
    How about indexes on that table while inserting these rows?

    Satya SKJ
  7. Martin Line New Member

    The source DB is SQL7 and the destination is SQL2000, but the service packs are all up to date.

    There aren't any indexes on either table, but that shouldn't matter anyway. In both cases the data is being moved from SVR1 to SVR2, so how come it takes so much longer to push the data than pull it [?]
  8. satya Moderator

    The Table Spool physical operator scans the input and places a copy of each row in a hidden spool table (stored in the tempdb database and existing only for the lifetime of the query).

    I think because of the version difference and in general linked server query is slow as compared, sort order/collation may have impact on performance between the servers.

    Try to follow tips as listed in thishttp://www.sql-server-performance.com/linked_server.asp link.

    Satya SKJ
  9. Martin Line New Member

    I've already checked out the tips page, which is very good but doesn't really help me. Using OPENQUERY doesn't really work because most of the remote work is done in the insert, and I've set the collation compatible flags.

    I've also just tried the same thing going from/to SQL2000 and I get the same problem. Is there any way of controlling the use of the spool table ?
  10. satya Moderator

    Are you aware of any network issues, firewall drops between these 2 boxes?
    I don't think we cannot control the table spool, but having more memory with optimum space on tempdb would help in gaining performance during the process.


    Satya SKJ
  11. Argyle New Member

  12. Martin Line New Member

    I tried a profiler trace and couldn't find any reference to sp_cursorfetch. When I look at the destination (remote) DB, it looks like it's doing the following;

    exec sp_reset_connection
    set implicit_transactions on

    exec sp_cursoropen @P1 output, N'select * from "SourceDB"."dbo"."TABLE_A"', @P2 output, @P3 output, @P4 output

    exec sp_cursor 180150000, 4, 0, N'ShadowDB.dbo.TABLE_A', @COL_A = 'VAL_A', @COL_B = 'VAL_B', @COL_C = 'VAL_C', @COL_D = 'VAL_D', @COL_E = NULL, @COL_F = NULL, @COL_G = NULL

    INSERT [ShadowDB].[dbo].[TABLE_A]([COL_A],[COL_B],[COL_C],[COL_D],[COL_E],[COL_F],[COL_G]) VALUES(@Param000004, @Param000005, @Param000006, @Param000007, @Param000008, @Param000009, @Param000010)

    with the sp_cursor and insert repeated a fair few times, and it certainly seems to be that which is eating up the time. I don't know if this helps anyone ?
  13. satya Moderator

    Before the SQL Server ODBC driver sends the command to the SQL Server, it restructures the ODBC call syntax to the SQL Server sp_opencursor command syntax.

    Internally, ODBC treats every record set as a cursor that you can manage on the client or on the server. Client-side cursors return all the data from the server by issuing one SQL statement that streams all the rows to a local cache on the client.

    Satya SKJ
  14. Argyle New Member

    Yea I meant sp_cursoropen and not sp_cursorfetch. Use pull of possible or try the technique with OPENQUERY mentioned in the link above. Not sure if it works in a push scenario though.

  15. Martin Line New Member

    OK. I tried openquery without success

    Thanks everyone for your help. It looks like I'm scuppered though....

Share This Page