Speed up Linked Server Connection To Oracle using OPENQUERY

Last time I showed how to create a connection to an Oracle database by creating a Linked Server in SQL Server.  Most of the time this works fine, but when you are pulling a lot of information, it tends to run slow.  By using OPENQUERY, the query will run very, very fast.

We have previously created a Linked Server called TEST (see link below)

Linked Server that connects to Oracle

that connects to our Oracle database so we can pull data into our DataWarehouse.  Generally this works well, but now we need to pull more information and performance is not that great.  Using the OPENQUERY command:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql

our query comes back very quickly.

The OPENQUERY clause runs the query on the originating server instead of being processed on SQL Server.  This way you use all of the resources on the Oracle server and the query runs just fast as if you ran it directly on the originating server.

Below are a couple examples and speed tests comparing the two methods.

TEST 1

Below I am just selecting all values for a certain city using the Linked Server TEST.  This brings back 9416 rows in approx. 31 seconds.  This query is processing on the SQL Server, but pulling the data from Oracle.

o1

 

Using OPENQUERY, the same rows come back in 1 second!!!  The syntax is as follows:

OPENQUERY ( linked_server ,'query' )

In our example TEST is the name of the Linked Server and the query is enclosed in quotes.  The important thing to remember is any literal needs to be enclosed between 2 single quotesAnother important item to remember is that Oracle is case sensitive so you need to make sure your literal is in the correct case.

 

o2

 

TEST 2

Another example is using the Linked Server data in subquery.  First here is an example using a straight Linked Server.  It is looking for any data in our staging database that exists in the source database.  Below you can see it took 28 seconds to retrieve the 9416 rows.

o3

Using OPENQUERY, we can see the results come back in approx. 2 seconds!!!  Below you just write the subquery like you normally would, but use OPENQUERY so the query runs on the Oracle server.

o4

 

If your Linked Servers are running slow against Oracle data, it is worth the time to investigate the use of OPENQUERY for huge performance gains.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s