Push or Pull? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Push or Pull?

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 07:31:42

I want to transfer records between two SQL boxes, connected by a relatively slow link.

The task is to upload stock levels and is something along the lines of


-- Get most recently received date
SELECT @MyCutoffDate = MAX(UpdateDate)
FROM   TargetServer.MyDB.dbo.StockTable

-- Update records changed SINCE this date
UPDATE TARGET
SET    StockLevel = SRC.StockLevel
FROM   SourceServer.MyDB.dbo.StockTable AS SOURCE
       TargetServer.MyDB.dbo.StockTable AS TARGET
       ON TARGET.PartNo = SOURCE.PartNo
WHERE  SOURCE.UpdateDate >= @MyCutoffDate

This will be sceduled to run each hour.

If there any difference (performance is the key, but maybe there are other considerations?) between scheduling this on the TARGET [pull] or SOURCE [push] servers?

I presume, because both ends are SQL Server, that the above "style" is fine and I don't need to consider using OPENQUERY or any other fancy style?

Thanks

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 13:09:49

It shouldn't matter what server you set it up on because the entire process will be handled as a remote procedure call, unless one server is significantly "better" than the other server. In that case, I would set it up on the fastest server just because that's where the work will theoretically be done.

If this is a lot of records though, it might be faster to find the correct records, bcp them into a staging table, then run the update against that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 13:20:38

The table has a lot of rows, but the "WHERE SOURCE.UpdateDate >= @MyCutoffDate" will be small - I presume the actual recodset is produced at the Remote end, and then ONLY the matching rows are transferred to the Target? (What I would call Client/Server<g>)

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 13:24:41

That would be correct. Of course, I would test it on both ends just to be sure. :)

Test

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 14:44:49

"Test"
Will this do?:


--Clean up after yourself.  gheesh
DROP TABLE kristen_test

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources