Hello. We all know the quickest way to export from Toad is csv. The largest file I am currently working with is 18 MB. Why does it take anywhere from 30 minutes to an hour to export? When looking at task manager it states “Not Responding”. Is there any way to fix this? I have asked for direct help through my company, now I am asking the Toad Community for assistance. I appreciate any suggestions and thank you in advance for your time.
Be sure to uncheck “display all results in grid”. That makes a huge difference on big exports.
When that option is checked, we use the query that is connected to the grid (and we can’t unconnect it). So you have to wait for all of the processing that goes along with keeping all rows in memory, displaying them in a grid, and scrolling. When “display all results in grid” is unchecked, we re-execute the query in a way that is not connected to any grid, and in a way that is unidirectional. So once we are done reading a row from the query, it is no longer kept in memory.
Thank you John. I did have that box checked. I just unchecked it, but it’s hung up and not responding on record 500.
Well, we fetch 500 rows at time. So it sounds like the first batch of rows has been fetched and exported, and now Toad is waiting for the next batch of rows to arrive. My guess is that the slowness is in Oracle working to execute that fetch. If a query is complex and the tables are large, joins can be slow and it can take a little while for Oracle to work that out. You’d have to tune your query or maybe add an index or something to make it faster. If there’s not really anything that can be done, another option would be to do a "create table
Thank you so much for your time and input John! I will try the create a table.
Can I ask what you are doing with the data once it is exported? Does it have to be a csv file?
No, it does not have to be a csv file - we just normally export in that format to make it run faster. We are comparing that data to data in another system to figure out why the two systems don’t balance.
Have you tried using Toad Data Point to do the comparison?
Toad Data Point allows you to connect to 2 ‘systems’ at the same time and do comparisons, reporting, transformations, profiling, etc., etc. The systems do not have to be the same, i.e. Oracle to Oracle. You can connect to virtually any data source, SqlServer, Excel, Access, Greenplum, DB2, Azure, Oracle, etc., etc. The list is long.
Heck, depending upon your data sources you may not need to connect to Oracle at all with this tool.
You can also save your query results to ‘local storage’ so you don’t have to connect to a database to view the data after the initial connection and by using Toad Intelligence Central you can share the results of your queries, reports, files, etc., with other team members across the business.
I just wanted to share this idea with you since getting to data is what most of us want and we have a way to get it from multiple sources.
There is a lot of information on the ToadWorld site, under Toad Data Point, about the tool set.
-Mark
I use a lot this Toad feature and works OK.
Ther might be two problems
- You session is somehow killed (network od db level)
- You have some network policy define on volume data sent through it (saw that once as well)
- Your query is really that slow (hug number of records)
For first two ask your DBA, OS, network support.
For 3rd option …
Same query in SQL*Plus runs how long?
put before query:
set echo on;
set timi on
set time on
Please try it and compare timing results.
P.S.
I saw many queries that retrieve first rows fast and then for other you wait a long time. SO SQL*Plus test is a god reference for that