best way to push records using linked server

Question

Post reply

best way to push records using linked server

mxy

SSCarpal Tunnel

Points: 4036
More actions
April 16, 2015 at 8:56 am

#296133

what is the best way to push records using linked server. below is my query on Source server
insert into LSDestserver.DB.dbo.tablename
select * from #temp order by abc desc
I initially thought to pull records from Source server to LSDestserver but its a temp table. I don't know whether it is possible or not. Is there any other way we can achieve this in optimal way.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Is there any other way we can achieve this in optimal way.

Use SSIS and throw away your linked server 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 2

thanks for your response that's a complete rewrite of current solution. anything we can tweak to make it faster?

Scott Ensink SSC Enthusiast Points: 156 More actions · Answer 3

We've done the same way you're asking, but usually limit to only insert what doesn't already exist.

How many records? Will you be doing this more than once?

_____________________________________________
Scott

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 4

yes more than once. if it a base table I would have pull the data from remote server which is also not optimized but it give some performance benefit

Scott Ensink SSC Enthusiast Points: 156 More actions · Answer 5

Depending on how many records, you could write a loop to push X at a time from the remote server to the main server.

You can also check if successful, and allow for re-run if not--would need to add a field to capture "was pushed" state. But then you would have to not store your pending data in a temp table, it would have to be a normal table.

There are lots of other options for keeping data synced between servers, depends on your requirements.

_____________________________________________
Scott

sql-lover SSCoach Points: 18530 More actions · Answer 6

Like Phil explained, performance will never be be optimal. SSIS or bcp is faster.

Having said that and if you can't change your code, I would suggest this read:

Linked servers and performance impact: Direction matters!

Usually, pulling data from your destination server is faster than pushing from the remote one.

Also, have you thought about implementing MSSQL replication, after making that temp table a persistent one? I am not a big fan of it, but depending of your table schema and amount of data, if may be easier and faster.

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 7

data is not in base table, after manipulation in temp table based on business logic we need to insert to a remote table. don't think replication in needed in this case.

I need to see if I can loop because we are inserting all records in temp table, if we need write a loop it would be more problematic since we need to check which records got inserted that would be a join back to remote server.

sql-lover SSCoach Points: 18530 More actions · Answer 8

mxy (4/16/2015)
data is not in base table, after manipulation in temp table based on business logic we need to insert to a remote table. don't think replication in needed in this case.
I need to see if I can loop because we are inserting all records in temp table, if we need write a loop it would be more problematic since we need to check which records got inserted that would be a join back to remote server.

That's what I'm saying.

You create a persistent and staging table then you create or insert your set there, instead of the temporary table. Once done, you can replicate that set daily or hourly to the remote server via MSSQL replication.

The other thing you can test is pulling instead of pushing the data and compare (see my post above) Test and check what's faster. But if you're using Linked server you will always have that limitation no matter what.

Based on my experience, when the set becomes really big, SQL server starts "choking" , traffic network between server increases , that's when you may start talking about app's redesign , SSIS or bcp,,etc, which are actually optimized for larger sets.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

mxy (4/16/2015)
what is the best way to push records using linked server. below is my query on Source server
insert into LSDestserver.DB.dbo.tablename
select * from #temp order by abc desc
I initially thought to pull records from Source server to LSDestserver but its a temp table. I don't know whether it is possible or not. Is there any other way we can achieve this in optimal way.

A very large part of the problem will likely be that nasty ol' ORDER BY.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 10

thanks Jeff i will check if that can be removed or not. i will compare both results and see if there is any difference in time taken to complete load