SQL Server Performance Forum – Threads Archive
Problems with Linked server update/insert
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
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
And may also checkhttp://www.sql-server-performance.com/linked_server.asp & KBAhttp://support.microsoft.com/default.aspx?scid=KB;en-us;Q243589 link for information. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I’m most certain that this answers will help me,
I shall true this as soon as possible, thank You. MagnusJ
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
Check books online for OPENQUERY topic for any pointers. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
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
Or other way is to use SPs and call to execute from other server. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
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 @[email protected] + ‘ set @MyError= @@ERROR’ — Execute UpdateStatement
Execute sp_executesql @SqlUpdateStment,N’@MyError int output’,@MyError = @Error output MagnusJ
Updates are a bit trickier. This article might help: PRB: Slow DELETE or UPDATE Against Non-SQL Linked Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;309182 /Argyle
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
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
]]>