hi, my company is forcing us to switch to Toad Data Point from Sql Server and I have a question regarding some of Toad’s features.
I was using the cross connection query feature and I was trying to dump the results of that query into a temp table, but cannot figure out a way to do that.
Is this possible?
Yes, but I have found cross connection queries to run slow. I like to use the import wizzard to pull data from one connection directly into another database (say Oracle query into SQL Sever database). Then in a second step, join to the rest of the SQL Severver data that I need. Put both steps in the Automation tool so that all I have to do run the automation job (or even better schedule the automation job for re-ocurring stuff so I don’t have to run anything, it just happens on schedule and emails me when it is done.)
I am New to Toad Data Point as well. I bought the Pro version to evaluate it for the rest of my institution. I have tried cross connections against 2 oracle instances, the local storage and one oracle instance and even loading the data in an access database and cross query with an Oracle instance. The faster it went was running 10 minutes to pull 19000 records which is way too slow. If any one knows a way to improve this, I would be really appreciate it. It not, then I will not recommend TDP for my institution since pulling data from cross platforms is 80% of our work.
Annie.
TDP is a query and reporting tool, but it can also be used as an ETL (or ELT) tool as well. You may need a scheduled nightly job that pulls together data that you need so it is ready to run queries on in the morning. 19K rows should take seconds to pull if you data is set up correctly. If you want speed I would stay away from cross connection queries, they don’t run fast on TDP or any other product I have used. I am assuming you are sorting through many more rows than 19K to get that 19K. Look at the time it takes for each query to run on each platform to pull the pieces that you need. If one part takes a very long time what you may need is an index on that database to speed up your query. Then pull the fastest parts on to the slowest database and run the final query there.
Here’s an article that explains how cross connection query works and provides a few optimization ideas. You may find it useful.
www.toadworld.com/…/how-to-write-a-cross-database-query.aspx