SQL 2005 - Oracle Queries Linked Server Extremely Slow

Question

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

If I run the request through a 4 part identifier, for example:

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

It takes more than a minute to complete. If I run the same query:

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

It ends instantly. Is there a parameter that I am missing somewhere to get the first request to run in a decent period of time? Or am I stuck using openquery?

Answer 1

In the first example, using the "dot" notation, the client cursor mechanism is used, and most things are evaluated locally. If you select from a large table and use the WHERE clause, the records will be deleted locally from the remote db. Once the data has been pulled through the linked server, only then the WHERE clause is applied locally. Often this sequence is a performance hit. Indexes on a remote database are mostly useless.

Alternatively, when you use OPENQUERY, SQL Server sends the sql statement to the target database for processing. During processing, any indexes on the tables are used. In addition, the where clause is applied on the Oracle side before sending the result set back to SQL Server.

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

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

One problem with using OpenQuery that you may have already encountered is single quotes. If the sql string sent to the remote db requires single quotes around the string or the date of the date, they must be escaped. Otherwise, they inadvertently terminate the sql string.

Here is the pattern that I use whenever I deal with variables in an openquery statement on a linked server to take care of a 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)