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