Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Please see this http://stackoverflow.com/questions/7753380/performance-implications-for-using-openquery-in-a-view question on stackoverflow, it's a cross over subject and would love a dba's POV

share|improve this question

1 Answer 1

up vote 15 down vote accepted


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


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.

FROM OPENQUERY(<linked server>, 
            'SELECT <column list> FROM MyTable') T1
     SomeLocalTable T2 ON ...
WHERE T1.foo = 'bar'

FROM OPENQUERY(<linked server>, 
           'SELECT <column list> FROM MyTable WHERE foo = ''bar''')
     SomeLocalTable T2 ON ...

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 setting collation compatible says it all

If this option is set to true, SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.

That 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.

share|improve this answer
Thank you, just knowing this makes my life a lot simpler! With a bit of a recode it solves my driver issues and means the end solution will perform better. Good times :) –  Rich Andrews Oct 17 '11 at 15:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.