August 6, 2013 at 3:19 pm
Grettings everybody, I looking for the one who can solve this issue!
I need to save data from Server1 to Server2, this data is created from a stored procedure because It is the result from other tables and this data must to travel to a Publicator for future Replication.
The problem is, when Server1 updates the table on Server2, is too slow. I do something like this:
insert into [Server1].[Base].dbo.table select * from #table
but if I do -> insert into table select * from #table. It is so fast! I testing this process on my laptop but I need use two servers.
I read this is why SQL Server scans the table from linked server. Now, I need to know if exists a way to do this without this scan!
Thanks!
PD: Sorry if my english was bad!
August 6, 2013 at 3:29 pm
Yes, "linked server" is an eloquent way to spell "trouble".
How much data are you trying to insert? What is the speed of the network connection between the two servers?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 6, 2013 at 5:25 pm
Actually, around 280000 rows and I make the test on my own laptop, but It will work on LAN.
Thanks
August 7, 2013 at 4:42 am
You could try reading over the network instead of writing over the network.
In other words, write your data to a permanent staging table on Server2 using code that runs on server2, then execute the following code on Server1:
INSERT INTO dbo.Table SELECT * FROM Server2.DB.dbo.StageTable
August 7, 2013 at 9:07 am
Thanks everyone!
After think about this problem! and thanks to that the planets were aligned, I found the light at the end of the tunnel! jejeje I find a way to solve it.
But I am working right now and I cant write my solution! I will when I get off work!
Grettings!
August 9, 2013 at 7:41 am
The solution: I created a SP on Server2 (this will be the Publicator) It executes the SP who create the data at the end SELECT this data, so the SP on Server1 return a Dataset, That data will be inserted on a temporal table and then will be inserted on the table from Server2, something like this:
CREATE PROCEDURE SP_server2
@PARAM1 INT
AS BEGIN
SET NOT COUNT ON
CREATE TABLE #TABLE1 ([COLUMNS DEFINITION])
INSERT INTO #TABLE1 EXEC [SERVER1].BASE.DBO.SP_SERVER1 @PARAM1
DELETE A FROM TABLAPUB A WHERE EXISTS(SELECT 1 FROM #TABLE1 B WHERE A.ID=B.ID)
INSERT INTO TABLAPUB SELECT * FROM #TABLE1
END
So, the initial way was sooooo slow and this solution is quicker.
I hope somebody with the same problem can use it.
PD: I hope my english was good!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy