How to query remote procedure or view

Question

How to query remote procedure or view

SQL006

SSCertifiable

Points: 5071
More actions
September 5, 2015 at 9:00 am

#311404

I have 2 server A and Server B(remote 3rd party) both SQL server 2012. We need to query the data from remote Server B, we are planning to use either view or stored procedure. The query contain multiple joins to fetch data from around 10 tables(minimum 10 tables is there in initial analysis may be more tables will be added in future).
If we are going to access the data through VIEW then we are going to write the below query
SELECT * FROM view-name WHERE Column1 ='abc'
My first question
Q1. I have a doubt whether this query will load all the data from table joined in the view and then the WHERE condition implement as in the VIEW definition we are not implementing the filter condition. If it is like that then this will be a performance issue, Please correct me if it is like this or not.
Q2. Which will has better performance a stored procedure or view.
Q3. How to call a remote stored procedure or View. linked server is not allowed.
Does the 4 part server.DB.Schema.SPorView name will work.
I had search and find about OPENROWSET/OPENQUERY does it works without linked server.
Please let me know if need further clarification
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

SQL006 (9/5/2015)
I have 2 server A and Server B(remote 3rd party) both SQL server 2012. We need to query the data from remote Server B, we are planning to use either view or stored procedure. The query contain multiple joins to fetch data from around 10 tables(minimum 10 tables is there in initial analysis may be more tables will be added in future).
If we are going to access the data through VIEW then we are going to write the below query
SELECT * FROM view-name WHERE Column1 ='abc'
My first question
Q1. I have a doubt whether this query will load all the data from table joined in the view and then the WHERE condition implement as in the VIEW definition we are not implementing the filter condition. If it is like that then this will be a performance issue, Please correct me if it is like this or not.

Test it. If the data is in the table and you can connect to it remotely, of course it's going to move the data. But test it to be sure.

Q2. Which will has better performance a stored procedure or view.

Yes.

Or, more precisely, a view is just a query. It doesn't store anything special. It's just a query. So running a query against a view, you're running a query against a query. It will not be more or less efficient than any other query against a query. The issues come into play on performance with views when you start joining one view to another or nesting views within views within views. The optimizer, instead of dealing with the 6-20 tables that you might actually need instead has to deal with 60 tables from the multiple views, figuring out which ones are needed, etc. It can be quite problematic for performance in that case.

However, at the base, a view is just a query. How well is that query written? It'll probably work just as fast in a procedure.

Q3. How to call a remote stored procedure or View. linked server is not allowed.
Does the 4 part server.DB.Schema.SPorView name will work.
I had search and find about OPENROWSET/OPENQUERY does it works without linked server.
Please let me know if need further clarification
Thanks

Linked servers can be called from procedures. You must have the syntax wrong. OPENQUERY can be a performance enhancer since you pass it filter values to reduce the amount of data coming back from the linked server. The one issue you can hit with linked servers is that, outside of OPENQUERY, you can get all the data moved across the wire and then filtered locally. That hurts performance. However, not seeing the code or your set up, I'm just speculating.

Test it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL006 SSCertifiable Points: 5071 More actions · Answer 2

Thanks Grant for the reply... had a discussion with the client they doesn't allowed to create linked server, what are the other options available can you provide any reference links.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You already have it. OPENQUERY is the other option to query across the line. Otherwise, you can look to SSIS to migrate the data.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning