SQL 2005 - Linked Server to Oracle Queries Extremely Slow

Question

On my SQL 2005 server, I have a linked server connecting to Oracle via the OraOLEDB.Oracle provider.

If I run a query through the 4 part identifier like so:

SELECT * FROM [SERVER]...[TABLE] WHERE COLUMN = 12345

It takes over a minute to complete. If I run the same query like so:

SELECT * FROM OPENQUERY(SERVER, 'SELECT * FROM TABLE WHERE COLUMN = 12345')

It completes instantly. Is there a setting I'm missing somewhere to get the first query to run in a decent period of time? Or am I stuck using openquery?

Answer 1

In your first example using "dot" notation, client cursor engine is used and most things are evaluated locally. If you're selecting from a large table and using a WHERE clause, the records will be pulled down locally from the remote db. Once the data has been pulled across the linked server, only then is the WHERE clause is applied locally. Often this sequence is a performance hit. Indexes on the remote db are basically rendered useless.

Alternately when you use OPENQUERY, SQL Server sends the sql statement to the target database for processing. During processing any indexes on the tables are leveraged. Also the where clause is applied on the Oracle side before sending the resultset back to SQL Server.

In my experience, except for the simplest of queries, OPENQUERY is going to give you better performance.

I would recommend using OpenQuery for everything for the above reasons.

One of the pain points when using OpenQuery that you may have already encountered is single quotes. If the sql string being sent to the remote db requires single quotes around a string or date date they need to be escaped. Otherwise they inadvertantly terminate the sql string.

Here is a template that I use whenever I'm dealing with variables in an openquery statement to a linked server to take care of the single quote problem:

DECLARE @UniqueId int 
, @sql varchar(500) 
, @linkedserver varchar(30) 
, @statement varchar(600) 

SET @UniqueId = 2 

SET @linkedserver = 'LINKSERV' 
SET @sql = 'SELECT DummyFunction(''''' + CAST(@UniqueId AS VARCHAR(10))+ ''''') FROM DUAL' 
SET @statement = 'SELECT * FROM OPENQUERY(' + @linkedserver + ', '  
SET @Statement = @Statement + '''' +  @SQL + ''')' 
EXEC(@Statement)