Pull or Push the Data

Question

Pull or Push the Data

rew-370421

Hall of Fame

Points: 3640
More actions
October 22, 2009 at 7:46 am

#212866

My database's data is gathered (via queries) from another (LINKED) SQL Server. Which is more efficient in gathering and then populating my database:
1) PULL: Running jobs/queries on my local SQL Server to that Remote (via LINKED) Server
2) PUSH: Running jobs/queries on the Remote Server and then it sends the results to my local Server where it can then IMPORT it.
I hope I'm making sense?
...thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I've seen both work well. A lot of it has to do with the relative power and load of the two servers. Remote queries can also be quite slow in some cases.

With large queries that involve data exclusively from one server, I've used SSIS to export the data into a set of text files, handling all the Joins, Where clauses, etc., locally, then ship the text file over the nextwork to the other server, and import it there. Worked very well in that case.

With another situation, I pulled the data from the other server, loaded it into temp tables locally, and then did all the joins there and then processed it into local tables from those. In that situation, the export to text was slower.

So, you have options, and you'll probably need to test a variety of solutions to find out which works best in your case.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

You may want to look into a thing called "Replication".

--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)

sjimmo SSChampion Points: 11139 More actions · Answer 3

In an ideal, client server environment, you should from the local server execute a stored procedure on the remote server. This will put the workload on the remote server, utilizing the resources there rather than dragging data back and forth across the network, utilizing bandwidth as well as additional resources on the local server. These resources will include CPU, network, memory as well as disk IO, especially on your tempdb.

Once the SP on the remote server is completed, only the requested data will be returned, thus reducing network bandwidth and usually being more efficient.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

rew-370421 Hall of Fame Points: 3640 More actions · Answer 4

Thanks for all the information.

sjimmo, that was my initial thought when I was first given this database to support.

Jeff, I'll look into "replication". I've never used it before.