Please see this question on stackoverflow :
I am using an EasySoft ODBC driver to link a SQL Server 2008 R2
Express instance to Interbase and I am having some difficulty with
getting metadata from the remote server. From looking about on the net
the main sugestions all mention using OPENQUERY instead of the four
part linked server syntax.E.G. My current (problematic) approach is…
CREATE VIEW [LIVE].[vwPRDETS] AS SELECT * FROM [LBLIVE]...[PRDETS] WITH (NOLOCK)
But on some tables I get the error when calling the view…
Msg 7353, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
linked server "LBLIVE" supplied inconsistent metadata. An extra column
was supplied during execution that was not found at compile time.Also, some views I cannot even create because I get the following…
Msg 7315, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
linked server "LBLIVE" contains multiple tables that match the name
""SYSDBA"."AUDIT_LBABKP"".Although there is only one of the tables mentioned.
The alternative approach from searching the net seems to be more
like…SELECT * FROM OPENQUERY(<linked sevrer>, 'SELECT <column list> FROM MyTable')
So, my question is, if I use OPENQUERY in my view definition will SQL
Server be able to optimise the resulting SQL being sent to Interbase?
Or is there really not much difference between the two approaches?
It's a cross over subject and would love a dba's POV.
Best Answer
Summary
Let the linked server do as much as possible.
It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server
Long
The key factor is where the query runs.
In this case, it is a trivial SELECT so all rows from a table will be sent down the wire. It doesn't matter.
When you add JOINs and WHEREs then it can matter. You want SQL Server to allow the linked server to do as much filtering as possible to reduce the size of the data coming over the network.
For example, the 2nd case here should be more efficient.
A limitation of OPENQUERY is that you can't parametrise: so you need dynamic SQL to add WHERE clauses etc.
Linked servers performance can be affected by
sp_serveroption
. The settingcollation compatible
says it allThat is, try not to let SQL Server process the data locally.
Note: On my
foo = 'bar'
2nd example above, the filter is sent to the linked server because it just a string constant to SQL Server. The real WHERE clause in the first example may or not be sent remotely.Finally, I've also found that staging the data into a temp table and joining that onto local tables is often better then joining directly onto the OPENQUERY.