I'm on Toad for Oracle 16.2
This query returns 9833 rows. The data grid shows 2000 rows were already fetched and took a couple of minutes to run. It took over 20 minutes to come back. The first time I exported the same dataset it only took a few minutes.
Not much to go on here, but I'll take a couple of guesses...
- FYI, retrieving rows from the database and exporting the rows that have been returned are two separate operations... might be the case that your query (analytical in nature, perhaps?) might take a while to retrieve the rows, but pretty quick to export them to a flat file.
- Did you retrieve (and export) the same number of rows as last time? (e.g. could be the case that you had selected by accident only a certain numbers of rows in the grid for export)
- With the same connection/method?
- Was the database busy with other activity?
- Network anomalies?
Literally, there could be any number of factors that cause differences in execution/retrieval/export times... again, just guessing at the causes here without additional info (like steps to reproduce, etc.)...
If you know you have a slow-running query with a limited number of rows (this case seems to qualify for both), it is faster to check "Display all results in grid".
Display all results in grid:
- When unchecked: Re-executes the query in mode which releases data from memory after scrolling past a row. This allows huge numbers of rows to be fetch with little memory consumption. It comes at a cost of re-executing the query, but is almost always the fastest option
- When checked: Uses the Oracle cursor which is already opened and attached to the grid to perform the export. This mode does not re-execute the query, but will keep all rows in memory as the dataset is fetched and exported (necessary to allow scrolling up/down in the grid). Also, because the cursor is attached to the grid, the process of scrolling through all of the rows to perform the export is a bit slower.
That said, it seems like in your case, Oracle was taking a long time to re-execute the query. If this is repeatable, maybe you can trace the session in the database to identify the cause of the slowness.
@JohnDorlon @Gary.Jerep thanks for the replies.
It's hard to say whether it was DB load that caused the delay. I had done the export minutes before without issue and I only added an additional filter condition the second time so there should have been fewer records. Populating the data grid took the same time but the export was much longer.
When I run a query from the editor I get a progress dialog that I can cancel. Can the same be done when running the query for the export?
Hi Raymond,
No, the export dataset internal query isn't cancellable, but I think it could be made to be so.
I'll log it as an enhancement request.
-John
1 Like
Thanks John, it's not so much the ability to cancel, it's more the progress indication instead of the Not Responding when I click on it.
Well, when the query is executing, we can't give you any progress info because we don't know how long Oracle will take to execute it.
Once it starts fetching though, you can see the progress at the bottom of the export dataset dialog, and at that point it is cancellable. But 10,000 records honest should export very fast once it makes it to the fetching stage. Unless there are CLOBs or something like that involved
What I meant by progress is the timer not the records retrieved. At least with the timer moving I know Toad isn't hung.
1 Like
They're actually kind of a package deal. I'll see what I can do.