SQL 2005-链接服务器到Oracle查询的速度极慢 [英] SQL 2005 - Linked Server to Oracle Queries Extremely Slow

问题描述

在SQL 2005服务器上，有一台链接服务器通过OraOLEDB.Oracle提供程序连接到Oracle.

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

如果我通过4个部分的标识符运行查询，如下所示:

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')

它立即完成.我是否缺少某个设置来让第一个查询在相当长的一段时间内运行?还是我在使用openquery卡住了?

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?

推荐答案

在第一个使用点"符号的示例中，使用了客户端游标引擎，并且大多数事情都在本地进行评估.如果您是从大型表中选择并使用WHERE子句，则将从远程数据库本地提取记录.跨链接服务器提取数据后，才在本地应用WHERE子句.通常，此序列会影响性能.远程数据库上的索引基本上变得无用.

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.

或者，当您使用OPENQUERY时，SQL Server会将sql语句发送到目标数据库进行处理.在处理期间，将利用表上的所有索引.此外，在将结果集发送回SQL Server之前，在Oracle方面应用了where子句.

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.

根据我的经验，除了最简单的查询外，OPENQUERY会为您提供更好的性能.

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

出于上述原因，我建议对所有内容都使用OpenQuery.

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

使用OpenQuery时，您可能已经遇到的难题之一是单引号.如果发送到远程数据库的sql字符串需要在字符串或日期日期前后加上单引号，则需要对其进行转义.否则，它们会无意中终止sql字符串.

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.

这是我在处理链接服务器的openquery语句中的变量时使用的模板，以解决单引号问题:

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) 

这篇关于SQL 2005-链接服务器到Oracle查询的速度极慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！