Remote insert performance

It seems that remote insert takes much more time than "local" insert.
My table MyServer.MyDb.dbo.MyTable has hudreds of milions of rows and
hudreds of GB. It has 3 indexes: clustered (not unique), nonclustered unique
and nonclustered (not unique).
An insert of tens or hundreds is imediate in MyServer. However it takes
minutes (cca. 1 minute per each 100 rows) when inserted with remote insert
--executed at OtherServer
insert MyServer.MyDb.dbo.MyTable select * from #TmpTable
(It does not make diffrence wether the select is from temporary or ordinary
table)
What is the reason? Can I influence it?
Thank you for commentsHi
The Network IO and RPC traffic involved in doing a remote call carry a lot
of overhead. This really slows the process down and is expected.
If you want ot compare it, create a linked server on a server to itself and
run a simular process.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"karuzo" wrote:

> It seems that remote insert takes much more time than "local" insert.
> My table MyServer.MyDb.dbo.MyTable has hudreds of milions of rows and
> hudreds of GB. It has 3 indexes: clustered (not unique), nonclustered uniq
ue
> and nonclustered (not unique).
> An insert of tens or hundreds is imediate in MyServer. However it takes
> minutes (cca. 1 minute per each 100 rows) when inserted with remote insert
> --executed at OtherServer
> insert MyServer.MyDb.dbo.MyTable select * from #TmpTable
> (It does not make diffrence wether the select is from temporary or ordinar
y
> table)
> What is the reason? Can I influence it?
> Thank you for comments