SQL 2005 - Linked Server to Oracle Queries Extremely Slow

sql-server-2005 tsql linked-server
John picture John · Aug 26, 2010 · Viewed 9.3k times · Source

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

Dean picture Dean · Sep 29, 2010

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)

Related questions

How to reference a sql server with a backslash (\) in its name?

Givens: One SQL Server is named: DevServerA Another is named: DevServerB\2K5 Problem: From DevServerA, how can I write a query that references DevServerB\2K5? I tried a sample, dummy query (running it from DevServerA): SELECT TOP 1 * FROM DevServerB\2K5.…

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More