November 7, 2017 at 4:34 am
Morning All,
I have a view 'RemoteUserView' on Server A that references a view on Server B 'UserView' via an openrowset. This is like this because (urgh its a rant) -- ultimately it cannot change:
Server B's UserView looks like this: create view UserView as select * from users;
Server A's RemoteUserView looks like this:
create view RemoteUserView as
select * from openrowset(server, select * from UserView)
My question is, when I query the RemoteUserView I do so with a filter/predicate like where UserID = 145202. In this case where does the filter get applied? Does it bring back the entire UserView onto Server A and then apply the filter, or does it do so at the source before bringing the rows back. The Execution plan suggests the former but I wanted to double check. I don't want to be in a state where its bringing back 100,000 rows just to find 1 user.
Cheers
Alex
November 7, 2017 at 4:20 pm
alex.sqldba - Tuesday, November 7, 2017 4:34 AMMorning All,I have a view 'RemoteUserView' on Server A that references a view on Server B 'UserView' via an openrowset. This is like this because (urgh its a rant) -- ultimately it cannot change:
Server B's UserView looks like this:
create view UserView as select * from users;
Server A's RemoteUserView looks like this:
create view RemoteUserView as
select * from openrowset(server, select * from UserView)My question is, when I query the RemoteUserView I do so with a filter/predicate like where UserID = 145202. In this case where does the filter get applied? Does it bring back the entire UserView onto Server A and then apply the filter, or does it do so at the source before bringing the rows back. The Execution plan suggests the former but I wanted to double check. I don't want to be in a state where its bringing back 100,000 rows just to find 1 user.
Cheers
Alex
It could be either - depends on where you have the where clause or rather the query you are sending to the remote server. The query part of the openrowset is processed on the remote server. You can also add a where clause after the query provided with openrowset and that would be filtered on the current server. It's the query you send across, what's in the parenthesis of the openrowset, that is processed on the remote server.
If you have the where clause in the query you are sending across, e.g.
select * from openrowset(server, select * from UserView where UserID = nnn)
that would filter on the remote server.
If you put it after the query you are sending across, e.g.
select * from openrowset(server, select * from UserView ) where UserID = nnn
then you bring it all across and filter it when it gets to the current server.
Sue
November 8, 2017 at 12:54 am
Thanks Sue.
Yes, I am filtering after the query then. As the where clauses exists outside of the OpenRowset query.
Which makes it not suitable, unfortunately, for my use. Which is good to know now 🙂
Cheers
Alex
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply