SQL Server accesses Oracle to query performance

1. The problem system has a module that needs to query data in the Oracle database. Currently, this is achieved by creating a linked server. The current query statement of limit is a simple

1. The problem system has a module that needs to query data in the Oracle database. Currently, this is achieved by creating 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 current query statement is a simple with wh

1. Problem

The system has a module to query data in the Oracle database. Currently, this is achieved by creating 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 statement with the where condition, similar to the following:

　　

SELECT * FROM LINKED_NAME..ACCOUNT_NAME.TABLE_NAME WHERE COLUMN1=SID;

The problem is that the query speed is very slow and the frontend reports a timeout. Therefore, we are prepared to improve the query performance.

2. Analysis

First, check whether the remote Oracle table has an index on the filter field. It has been confirmed that an index exists.

Then, the execution plan of the statement is viewed in the SSMS client, as follows:

　　

From the execution plan, we can see that the process is not as imagined. I thought that SQLServer would submit the entire query to the Oracle server for execution. In actual remote query, the where condition is not added, but the result is returned to the local machine and filtered locally (with a filter)

This speed will certainly be very slow, because the query does not use index search, you need to transmit all the remote data to the local machine before filtering, equivalent to full table scan, more time for network transmission.

3. Solution

In fact, as long as the query statement with the where condition can be submitted to the Oracle server for remote query, the performance problem can be solved.

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

　　

However, we can find another way to pass query parameters by splicing dynamic SQL statements.

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

After optimization, the execution plan becomes as follows: You can see that the number of actually returned rows is small.

　　

The query speed is significantly improved, from the original frontend timeout to the current millisecond level.

Little proud ^ _ ^, welcome to make a brick. If you have other methods, please share them. Thank you!