May 12, 2006 at 11:37 am
Overview:
We are linking a two tables CREDIT (350K rows) with another table LOCKBOX (450K rows) within the same Oracle DB/schema using SQL Server 2000 and a linked server. The column being linked is a numeric column named ID. Both tables and the metadata table have the proper indexes so that is not a limiting factor. Oracle 9.2 client, 9.2.0.4 OLEDB driver. We are running SQL Server SP4 ver. 8.0.2189 on the InfoBroker.
1. I've created a sample SQL query that joins the tables. When I run it through Oracle's SQL Plus it returns rows immediately. When I run it through SQL Server Query Analyzer, it takes 1 min. or so.
3. I've created a small test application and I've run my test SQL query directly through the Oracle OLEDB driver bypassing SQL Server all together and it runs in ~2 seconds.
4. I've tested the query with Oracle OLEDB, MS OLEDB for Oracle and OLEDB for ODBC. All have similar results.
We've ran traces on the Oracle system and look at the query execution plan in SQL Server. The problem is that SQL Server is returning all rows for the table LOCKBOX and hashing the rows with the rows returned from CREDIT. It is not simply passing the query directly through the Oracle OLEDB driver. We've tried all different combinations of Oracle linked server connections with no luck. We're stuck using the current Oracle client, OLEDB & ODBC driver versions and a linked server because of external factors.
Any ideas on speeding up our query?
May 15, 2006 at 8:00 am
This was removed by the editor as SPAM
May 15, 2006 at 1:26 pm
You could use openquery.
Create a view on oracle that combines both tables?
May 15, 2006 at 1:43 pm
Thanks for the suggestion. I've found the solution though:
When a new linked server is created, the Oracle OLEDB driver options can be set. Once the option "Nested Queries" was switched on it worked. It sent the join query to oracle in an efficient manner. Also, the linked server option "Collate" is set that may or may not have an effect on it. I'm going to test further.
I'm sure OpenQuery would have worked, but it is actually a third party application that is creating & sending the query and it does not use that function. Just trying to work within the parameters we have.
May 16, 2006 at 2:55 am
That made me curious since we use also Oracle at work.
How do you set the option "Nested Queries" to on?
May 16, 2006 at 6:52 am
1. Start creating a new linked server.
2. Select the OLEDB provider.
3. Click the OLEDB Provider Options button.
4. You do not have to complete the linked server for the options to take effect.
5. SQL Server books online has more info about the other OLEDB options and linked server options.
This sets the OLEDB provider options for all linked servers using that OLEDB provider.
SQL Server 2000 is touchy when it comes to accessing Oracle through a linked server. If a table has been altered, you may receive errors relating to compile-time vs run-time column settings (length, data types, etc.). This is a known issue and the only workaround that I know of is to re-create the table with the new column info.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply