|
|
Posted by Erland Sommarskog on 10/10/05 00:38
Ootyguy (npravin@hotmail.com) writes:
> Trying to do this all day and googling for answers but found none, hope
> someone can help. Thanks in advance.
>
> select * into
> OPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test)
> from LocalTable
>
> Reason: I am joining local tables with linked server tables using the
> format "LinkedServer.database.owner.object" to execute a query, it
> takes forever to execute since the tables joined on the remote servers
> have more than 50Mil records. I read somewhere that sql server needs to
> copy the tables locally to the temp db and does the join there, hence I
> was hoping to dump the data of the local database into a temp table on
> the remote server and then do a join with OPENQUERY, which will execute
> the query on the linked server and return the results.
It may copy the entire table over the wire - but not by necssity.
What you try above is a dead end. What you could consider is to
optimize by hand, so to speak. If you know that you only will retrieve
a handful of those remote rows, then get those rows into a local table,
and use OPENQUERY, to be sure that it is a pass-through query.
Having 15 tables from 15 servers in the same query sounds like a nightmare
to me. Maybe you should consider replication to get the data into one
spot.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|