How to query a local and remote database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to query a local and remote database

Author

Topic

kjk_kjp
Starting Member

18 Posts

Posted - 2010-05-18 : 10:46:14

I have the following query - it runs, but the performance is terrible (takes a couple of minutes to run)
Table SECTIONS is on a remote server
Table MeetingInfo is on my local server/database

SELECT a.SYNONYM, b.LOCATION
FROM [RemoteServer].[DatabaseName].[dbo].[SECTIONS] a, [dbo].[MeetingInfo] b
WHERE a.ID = b.ID

I have tried the following, but I get an error (Invalid Object Name 'MeetingInfo')
EXEC('SELECT a.SYNONYM, b.LOCATION
FROM [DatabaseName].[dbo].[SECTIONS] a, [dbo].[MeetingInfo] b
WHERE a.ID = b.ID') AT RemoteServer

Any ideas?

Berend
Starting Member

2 Posts

Posted - 2010-05-19 : 05:05:31

Depending on your situation there are some options, some not so nice..

Your main problem is reading all he records on the remote server.

If you can limit the data you want to see on the remote database
create a view that makes these limitations first (like: only records in this year) this will increase speed as it forces the where clause to be executed remotly first

Workaround 1 :
Use replication to replicate the remote table to your localdatabase
you can now query the data locally

Workaround 2 :
Use a maintenance plan to copy the new entry's from the remote table every night in to a local table
union this with a view limited to the records for today on the remote table and join on it. This will reduce data transfer to the newest records only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources