April 13, 2009 at 7:06 am
I've just come across a job that takes a long time to run and want to improve it.
Currently it runs on ServerA and runs a lot of SQL using a linked server to ServerB.
Basically it runs these kinds of statements in a stored procedure on ServerA
Insert into #temp from TableA_2 where ......
Insert into #temp from TableA_2 where ......
Insert into #temp from tableA_3 where ......
.
--Now push #temp to Linked ServerB
.
Insert into ServerB.DatabaseB.dbo.TableB from #temp
.
--Then it does a bunch of updates on ServerB, using only ServerB tables, but run from ServerA.
.
Update ServerB.DatabaseB.dbo.TableB set fieldb=fieldb_2
from ServerB.DatabaseB.dbo.TableB_2
join ServerB.DatabaseB.dbo.TableB_3 on ...
join ServerB.DatabaseB.dbo.TableB_4 on ...
.
.
Update ServerB.DatabaseB.dbo.TableC set fieldb=fieldb_2
from ServerB.DatabaseB.dbo.TableC_2
join ServerB.DatabaseB.dbo.TableC_3 on ...
join ServerB.DatabaseB.dbo.TableC_4 on...
Would it be quicker to create a stored proc on ServerB with the ServerB updates in it, then on ServerA, after building the #temp table and pushing to ServerB, I run:
exec ServerB.DatabaseB.ServerB_StoredProc
April 13, 2009 at 9:02 am
never understood the point of doing it
if you do it over the network there is network traffic overhead and you're probably using tempdb a lot more to cache the data
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply