Expedite Saving Data via Toad

Bert Scalzo — Tue, 06 Nov 2007 16:14:00 GMT

Probably one of the most useful and frequented tasks performed with Toad^® for Oracle is saving data to a file, such as comma delimited text (or any of the other numerous formats supported). It’s a relatively easy feature to find – you simply depress the “right hand mouse” (RHM) while anywhere within the displayed data grid and choose the “save as” option as shown below, but what if you could make that save operation run 10X faster?

OK – maybe not truly 10X faster, but much faster than by default – and much faster on PCs with limited or stressed memory resources (for those power users who like to run 50 applications at once on their poor old PC). In these specific cases, as well as in general, this little trick can make all the difference in the world. Let’s look at the “Save As” dialog options normally presented (see below).

I have asked for a comma delimited text file named “bert.txt” placed in my “c:\temp” directory. That’s pretty easy and where many people stop (i.e. they just press the OK button). Now look at the option for “Display all exported results in the grid” – what does this option mean and do? Note that this option used to be called “Clone Cursor” in prior versions and was defaulted to unchecked. Regardless of which Toad version and option verbiage, this option is simply asking “Should Toad display the entire exported data set within its data grid (and thus in memory) as well as write it to the file? The default in all versions regardless of what the option is called equates to yes – or, kill my PC’s memory.

Think about that – because this is a hugely critical question. Let’s say I’m going to export a data set with one million rows. With display results in grid = checked (or clone cursor = unchecked), I’m asking Toad to write the million rows of data to the text file and display it within my live data grid. So for each row, Toad has to write a record to the text file and add another row to the data grid. So it’s doing two things – one of which can be memory intensive. So the “Save As” takes a long while to run – and Windows task manager will show the Toad process as using a rapidly increasing amount of memory.

If I now repeat the “Save As” operation with display results in grid = unchecked (or clone cursor = checked), I’m asking Toad to only write the million rows of data to the text file and not to display it within my live data grid. So now the “Save As” operation is doing just one task – and not consuming lots of memory on my PC. Thus it runs oodles faster.

So what’s the downside (if any)? In this second example Toad simply creates a second cursor through which to save the data without copying it to the live data grid. Thus Toad will create and use a second cursor resource. That’s not too painful – but it requires that Toad has to re-execute the query, which could take some time. However, the time gained will generally exceed this extra time. Besides, that work is being done on the faster server.

The only real down-side is that my live data grid does not contain the million rows – so if I now scroll down in my data grid, Toad has to fetch those rows. But I know my poor old brain cannot read through and appreciate more than say two dozens rows of data. So in my case, absolutely nothing lost and everything gained.

Bert Scalzo's Blog

Expedite Saving Data via Toad