SQL Server access Oracle Query performance Problem Resolution

Original: SQL Server access Oracle Query performance Problem Resolution

1. Questions

　　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 to Oracle implementations 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 WHERE COLUMN1=SID;

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

2. Analysis

First, determine whether the remote Oracle table has an index on the filter field. It is confirmed that there is an index.

Then, the execution plan for the execution statement is viewed in the SSMs client as follows:

　　

As you can see from the execution plan, the process did not assume that SQL Server would submit the entire query to the Oracle servers for execution. Instead of adding the where condition to the remote query, instead of returning the result locally, the filter is executed locally (with a filter)

This will certainly be very slow, because the query does not use the index lookup, you need to transfer all the remote data to the local before the filtering, equivalent to full table scan, but also more network transmission time.

3. Resolve

In fact, you can resolve performance issues by committing query statements with a WHERE condition to the Oracle server to perform remote queries.

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 pass-through query parameters by splicing dynamic SQL.

　　OpenQuery use parameters 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 number of rows actually returned is very small

　　

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

A little proud of ^_^, welcome to shoot bricks. If there are other methods, please share, thank you!

SQL Server access Oracle Query performance Problem Resolution