You are here: Re: Temp Table on Linked Server « MsSQL Server « IT news, forums, messages
Re: Temp Table on Linked Server

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]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация