Toad World® Forums

How to save large Query Results in 6.2


#1

Hi
We need to run on a regular basis queries that deliver large data sets and save them as csv/txt files.
With the version 5 this was no problem, the little box where the file name is given took a long time to appear but the saving was possible.
In version 6.2 the little box appears immediately but then the whole thing crashes because it tries to download first everything on the screen (you can see it by the memory usage).
How can I achieve this with the new version?
I was hoping 6.3 had it;but already tried it and it still does not work, I wonder if there is a variable to be set so it does not download it onto the screen but directly to file as in V 5.
Thanks in advance
Ana Isabel

Message was edited by: AnaIsabel


#2

I have no problem retrieving data sets over 700.000 records, but SQL Nav fails to save them in a Excel proper format, so all I get is 65535 records in Excel 2007. CSV/TXT are fine, I think.


#3

I think the excel limit of 65,536 rows was inforsed in sql due to the limits of excel versions 97 till 2003 (65,536 rows by 256 columns).

“The Excel 2007 “Big Grid” increases the maximum number of rows per worksheet from 65,536 to over 1 million, and the number of columns from 256 (IV) to 16,384 (XFD).”
(source: http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel)


#4

Hi Ana,

How large was your dataset? Does it contain any BLOB or CLOB objects?

On a side note, SQLNavigator still have problem exporting more than 65000 rows to excel even when a later version of excel is used. This CR is in our system and will be fixed in a future release.

Gwen


#5

Hi, thank you for your replies…

I save it onto a txt file without opening. It is later imported into another DB.
The latest file was 550 MB with about 1.6 million rows
There are no CLOBs just “normal” text/number/date fields

Like I mention in V5 this is no problem, I did some searches before but all answers came for TOAd (and very old) that there is a “cursor” setting where you can define whether it is saved directly onto file, or fetched to screen and saved. I assume V5 is directly to file and 6.2 on screen first. I have the button FetchAll turned to OFF and we have tried both possibilities.

Any ideas?

Ana Isabel


#6

Thanks Ana for your information. Unfortunately we don’t have any setting for this feature. I will put this in the list of issues that need to be fixed.

Cheers,
Gwen


#7

Hi Gwen,

This is for us a very important feature V5 provided, with the risk of sounding redundant: is there no way then to load the information directly onto file?

It was a main reason why this tool was acquired, how soon do you see this fixed?


#8

Hi Ana,

The difference between 5.5 and 6.x is that for Data Export we now always load the entire dataset into memory, convert it to the required format and then save into a file, whereas 5.5 for some formats did conversion and writing row-by-row. Therefore, 6.x requires for those formats generally more memory to do export. I’m looking now how to make it work similarly to 5.5. If it doesn’t require fundamental changes to the code, this request is likely to be approved for 6.3.

Regards,
Roman


#9

Hi Roman, thank you for your reply.
Can you let me know through this post how it goes?
Or where should I look it up?

BR
Ana Isabel


#10

Ana,

We will keep you posted through this post.

Gwen


#11

Hi Ana,

I’m sorry to let you know that we haven’t implemented the complete change in 6.3 yet. Changing the behavior of the result set is at high risk and we don’t want to make it in 6.3 release. We’re trying to address this for you in the first beta we will send out for next release(6.4).

But we still have improved the process of saving the results to file. Once the records are fetched, the application is able to save them into file with lower memory consumption then before. This may not be able to solve your problem but we hope it will enable people to save more records at one time.

Thanks,
Vincent


#12

I am very sorry to hear that, I will try the beta, but in the meantime, where can we download Version 5?
We need to save large queries.

Please let me know so that I can download it.
Cheers
Ana Isabel


#13

Hi Ana,

Sorry for a late reply. You can get access to versions download from Quest Support Link. Just click on Quest Support Link area (under the Addtional pages area) on the left when you see this message.

If you would need any help getting access the previous releases installer, please contact our Support team who will be happy to assist you with it.

Thanks and regards,
Bruce