Tuesday, March 17, 2009

Linked Server collation, performance and temp tables

When I create a linked server to a non SQL Server platform or to a SQL Server instance that's using another collation I always use the following linked server options:

- Collation Compatible: False
- Use Remote Collation: False

In all other cases set collation compatibility to True, so indexes on the remote SQL Server tables can be usedleading to a significant performance boost.

I usually set the RPC / RPC Out and Data Access properties to True.

(click to enlarge)

When the linked server is a read-only data store I definitely prefer to use an OPENQUERY construction:

select * from openquery

SQLSERVER2,'SELECT col1,col2 FROM CodeCatalog.dbo.recommendations'

over the four part name equivalent:
SELECT col1,col2 FROM SQLSERVER2.CodeCatalog.dbo.recommendations

Far less distributed transaction errors (none so far!) generated by the OLE-DB driver.
Using OPENQUERY requires a bit more code, but it really, really pays of...

Because linked servers are not the fastest objects on the planet, try to access them as less as you can. I'll usually access them just once and dump te result in a temporary table and use that as input for further processing.

create table #t (id int)

insert into #T (id)
select * from openquery
SQLSERVER2,'SELECT id FROM CodeCatalog.dbo.recommendations'
...process results from #T here....

Don't forget to script your linked server definitions and store them somewhere safe.

also, check out my post on msdtc.

Bookmark and Share