I am using Toad for Data Analysts 3.1, and I have a query that is a very simple SELECT with an INNER JOIN on 2 different data sources.
An hour later, after getting a “Microsoft Access” error, I discovered that in the properties of my Oracle connection, the ODBC Driver needed changed from to my 11g driver.
Now, as my query continues to churn and churn… I’m wondering if I also need to do something with the SQL Server connection. The properties currently say .
Are these settings optimal for performance? I’m joining 900k rows on SQL Server to 60.0 MM rows on the Oracle connection. Joined on a single Primary Key.
My understanding of TDA cross-connection queries is that all rows from each connection query will be pulled into the local MySQL database, installed by Toad, and joined there.
You may want to do some testing with smaller query result sets and possibly re-think your overall strategy.
Thanks, that information on what’s going on behind the scenes really helps me a lot. I ended up hitting the same error ODBC Microsoft Access Driver - The size of the query result is larger than the maximum size of a database (2 GB)
I guess this sort of application is too big for Toad, I will try an ETL solution such as SSIS to see if that works better.
If you have Oracle write access you might consider copying the 900K row SQL server table to the Oracle database using the TDA Import wizard which is plenty capable for this task.
I would suggest using TDA Pro edition. We wrote our own optimization engine for Cross-connection queries. The Base editions uses an Access database to do the work and is not effecient for the size of dataset you need.
The Pro edition will execute and filter rows on the server. If you only have Base key I would still download TDP 3.2 trial and see if that works better for you.