Using T-SQL with Oracle DB

Question

I am using T-SQL query directly in Microsoft SQL Server Studio's Query window against to a large Oracle DB, and my SQL server is 2005.

I have created a linked server as myOracleServer. By using the following T-SQL query:

SELECT COUNT(*) FROM myOracleServer..owner.myTable WHERE id = 1000 AND Dt = '2009-02-26'

It take more than 1 minute to make just one call. For a small size table it is OK, but myTable on Oracle side is very large with millions of rows data.

What I found out is that I could use OPENQUERY to make a similar call with SQL query as a pass-through one. The result is very fast. The execution time is 00:00:02!

SELECT * FROM OPENQUERY(myOracleServer, 'SELECT COUNT(*) FROM owner.myTable WHERE ...');

The problem I have is that the query is not a constant string. I may change id and Dt values in the query, something like:

'SELECT COUNT(*) FROM ownwer.myTable WHERE id = ' + CAST(@id AS VARCHAR)...

OPENQUERY does not support a variable as query string nor expression.

Is there any other way to get the pass-through query to Oracle with fast performance?

Answer 1

Try something like this

DECLARE @sql varchar(2000)
SET @sql = SELECT COUNT(*) FROM owner.MyTable WHERE id = " + CAST(@id to varchar(9))
SELECT * FROM OPENQUERY(myOracleServer, @Sql)

Answer 2

Have you tried creating a View based on that query? If that works you should be able to use the View as if it were a local table.

Answer 3

I think I figure out a solution to resolve slowness issue when I try to pass a query from SQL 2005 server to an Oracle db.

There are three ways to do it. The first one is something like:

SELECT COUNT(*) FROM myOracleServer..owner.myTable 
  WHERE id = 1000 AND Dt = '2009-02-26'

If the table on Oracle side is a big one, with 3M rows of data for example, the execution time is very long. Today I tried it again on my SQL server and it took about 2'44" just for one query (maybe the table is very busy during business days).

The second method is to use OPENQUERY, as I stated in my question:

SELECT COUNT(*) FROM OPENQUERY(myOracleServer, N'SELECT COUNT(*) FROM 
  owner.myTable WHERE id = 1000 AND Dt = TO_DATE(''2009-02-26'')');

It is very fast. I run it again and the execution time is 00:00:00, stunning fast! However, the problem with this method is that OPENQUERY does NOT support variable as a query.

Actually, I found this method and was very exited about the result late at work yesterday. I wrote a blog on this issue last night. This morning when I tried to put this into production (a stored procedure), I could not use OPENQUERY since in my case, a variable query has to be built on id and date.

The good news is that I found the third way, an excellent solution to the issue:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT COUNT(*) FROM owner.myTable WHERE id = ' + 
  CAST(@id AS VARCHAR) + N' AND Dt = TO_DATE(''' + @dt +
  N''', ''yyyy-mm--dd'')');
EXEC (@sql) AT myOracleServer;

The key point here is to use EXEC with AT to specify a remote server or linked server, and don't forget to use () to bracket @sql variable. The execution time is 00:00:00!

My bog will be updated this evening.