Referencing tables on a remote server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: speegee (speegee_at_discussions.microsoft.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 07:47:05 -0700

I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,

select id, name from remoteserver.db.dbo.table1

In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,

create view v_table1 as select id, name from remoteserver.db.dbo.table1

and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?

Relevant Pages

  • SecurityFocus Microsoft Newsletter #152
    ... MICROSOFT VULNERABILITY SUMMARY ... Real Networks Helix Universal Server Remote Buffer Overflow ... ... NEW PRODUCTS FOR MICROSOFT PLATFORMS ...
    (Focus-Microsoft)
  • SecurityFocus Microsoft Newsletter #140
    ... Cafelog b2 Remote File Include Vulnerability ... Webfroot Shoutbox Remote Command Execution Vulnerability ... Pablo Software Solutions Baby POP3 Server Multiple Connection... ... Microsoft Windows XP Nested Directory Denial of Service... ...
    (Focus-Microsoft)
  • Re: Separate locations
    ... Yes, SBS 2003 and Server 2003 can co-exist in the same domain, and you can ... have multiple domain controllers in an SBS domain, ... Have I mentioned how much I like the Terminal Server solution for remote ...
    (microsoft.public.windows.server.sbs)
  • RE: Remote Office Configuration Suggestions?
    ... The additional DC at the remote site, could not be the SBS server, as you ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • RE: SBS 2003 sudden services problem over router based vpn
    ... I understand that your remote cannot receive POP3 emails through VPN ... SBS Server through routers. ...
    (microsoft.public.windows.server.sbs)