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?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> 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?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>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?
>.
>
