Remote insert performance

2 Sep 2005 11:22 AM

karuzo

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 comments

2 Sep 2005 12:36 PM

Mike Epprecht (SQL MVP)

Hi

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/

Show quote

"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 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 comments