Using OPENQUERY for Oracle DB

Today I googled an alternative solution to run an Oracle PL/SQL query or stored procedure from Microsoft SQL server 2005. I am very impressed by the result of this alternative way.

What I mean by an alternative way is that I have been using T-SQL queries directly against linked servers defined in Microsoft SQL server. A linked server is a server added to Server Objects->Linked Servers by using Microsoft SQL Sever Management Studio tool.



The linked server that Microsoft SQL server supports covers a variety of database servers, including Oracle server. By using T-SQL queries, you can easily query data from to any database object such as a table or view on this server in the same way as querying data from a local table or view, as in the following example:

SELECT COUNT(*) AS MyCount 
  FROM myOracleServer..owner.tableName 
  WHERE ...;

where myOracleServer could be a linked server to an Oracle server.

One problem I troubles me is that when a table on Oracle side is very big(with millions of rows of data), the query may take a while to execute, sometimes more than 1 minute just for a query call. I had many cases to take more than 10 hours to update data from my Microsoft SQL server to an Oracle server with thousands of T-SQL calls(checking, updating and inserting data).

The alternative way I found today is to use OPENQUERY call like this:

SELECT MyCount 
  FROM OPENQUERY(myOracleServer,
    N'SELECT COUNT(*) AS MyCount FROM owner.tableName WHERE ...');

The execution time is 00:00:00 comparing 00:01:09 with the similar direct T-SQL query as above. It was a stunning when I saw the result back right away.

The information I found is from a discussion on Bytes web forum, where one person responded a recommendation by using OPENQUERY in a Google Groups' discussion. Then I figured out the solution that I need to improve my current T-SQLs.

The reason for such a big difference, I think o as my understanding, is that the SQL server may create a temporary table or allocate a cache for the database object referenced by a linked server in a T-SQL' query, while OPENQUERY is just a OLE DB connection call to the linked server with a SQL query by which the server or Oracle does its job with its full power.