SQL Server access Oracle Query performance Problem Resolution

1. The question

The system has a module that needs to query the data in the Oracle database. It is now implemented by establishing a linked server.

SQL Server access Oracle implementation can refer to this article http://www.cnblogs.com/gnielee/archive/2010/09/07/access-oracle-from-sqlserver.html

The current query statement is a simple query with a where condition, similar to the following:

Select*from Linked_name. Account_name. table_name WHERE COLUMN1=SID;

The problem is that the query is very slow and the foreground report times out. So prepare to upgrade the performance of the query

2. Analysis

First, determine if the table for remote Oracle has an index on the filtered field. After confirmation, there is an index.

The execution plan for executing statements is then viewed in the SSMs client, as follows:

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

As you can see from the execution plan, the process is not what it was supposed to be, and it was assumed that SQL Server would submit the entire query to the Oracle servers for execution. Instead of a where condition when the remote query is actually in place, the result is returned locally, and the filter is performed locally (there is a filter)

This speed is certainly very slow, because the query does not use the index lookup, the need to transfer all the remote data to the local before performing filtering, equivalent to full table scan, and more network transmission time.

3. Address

In fact, the performance problem can be solved by submitting a query statement with a WHERE condition to the Oracle server to execute the remote query.

However, the problem is that OPENQUERY does not support parameters. See Microsoft Msdnhttp://technet.microsoft.com/zh-cn/library/ms188427.aspx

But fortunately we can find a way to implement the pass query parameters by splicing dynamic SQL.

OpenQuery use parameters can refer to this article: http://www.cnblogs.com/Dannier/archive/2011/09/21/openquery.html

When optimized in this way, the execution plan becomes as follows: you can see that the actual number of rows returned is very small.

The query speed is increased significantly, from the original foreground timeout to the current millisecond level.