Toad World® Forums

TDP 3.5.0.2936 receiving Out of memory error when exporting to Excel in Automoation

I recently upgraded to TDP 3.5.0.2936 from TDP 3.4.0.2038. I have never received this error in 3.4. The SQL query currently returns around 210K rows. I noticed there is a Check system resources check box I have unchecked that , but when I go back in to edit it is checked. Let me know if you need any more information as this is the only report stopping me to fully upgrade to 3.5.

You are correct, that option does not seem to be getting serialized. I entered QAT-2771 for this.

But when this option is on you would get this error in your automation script.

Export_1 - Program memory is too low. 408 MBs available. Reduce size of work load or increase RAM.

Are you getting this? Or actually Out of Memory error.

Also, note, Excel does not handle large numbers of rows well. To export large number of rows I would export as CSV. In excel all the rows have to be loaded into memory before exporting. With CSV, we export 500 rows at a time, so memory is not an issue.

I am sendng the Log file for 3.5 in a separte post I will send the log file from 3.4. I understand the large rows in excel and might think that is the problem if it didn’t work in 3.4. i would use CSV, but that is not what my users want. Maybe we can find out what has changed between 3.4 to 3.5 that takes more memory.
3.5 Corp Tagging.log (41.2 KB)

here is the 3.4 Log
3.4 Corp Tagging.log (35.8 KB)

I agree with your observation. Something is different between the two releases. I entered QAT-2807 to investigate this issue. Check back in a week to see what we found.

Debbie any status.

Only update I have is that we have set aside time for development to look into this. This work is being done now but so far no break throughs to announce (we just started on this and this is holiday weekend)

We noticed two options in your export. Your Read Buffer Size =“1000000”. This seems very large. If you have a lot of other windows open in TDP you may not have enough memory for this. Can you change back to the default of 5000? Also, you are appending the data as a new worksheet each time you export. Is that really what you want to do? It was great when Excel removed the 65k row limit but Excel is not a unlimited file. Can you change your option to either Overwrite the file each time or write to a named sheet each time. Try these options and see if this changes things. The issues may not be TDP 3.5 issue but these options.

Woops, It does look like you have overwrite on. Just try to change the Read Buffer size.

Changed buffer size to 5000 still receive Out of Memory.

Please unsubscribe me

Sent from my iPhone

On 28 May 2014, at 19:39, “scott.noggle_625” bounce-scottnoggle2013@toadworld.com wrote:

RE: TDP 3.5.0.2936 receiving Out of memory error when exporting to Excel in Automoation

Reply by scott.noggle_625
Changed buffer size to 5000 still receive Out of Memory.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

Scott,

We added some fix to current 3.6 code, would you like to try this one off to see if it works?

community-downloads.quest.com/…/ToadDataPoint_pro_Trial_3.6.0.bugs.664.zip

If you are still experiencing the issue, could you please send us the support bundle so we have better understanding of your environment.

Downloaded 3.6 and still received Out of Memory. I even unchecked the Check System Recources check box.
ToadSupportBundleToad Data Point.zip (686 KB)

Scott,

We are still investigating the issue, what we found is that if we run 3.4 several times, we will get out of memory exception too. Could you try restart your system and try again see if it helps? If possible, we would also suggest that you export large data to csv format which has better memory performance.

I have restarted same error. So with TDP I am limited to around 200K rows to export to excel. Even though Excels new threshold is over 1M rows. Another question., it does not seem the Check System Resorce box works, becuase I would think if my machine was Out of Memory I would see applications failing during this time. So TDP must not be utilizing all of the memory on the machine.

The resource check just checks for memory running low and tries to prevent running out of memory. In your case you really are running out of memory. We are going to start on some memory profiling tomorrow and look deeper into this but we suspect that the real issue is a third party dll that does not stream the Excel data. That is why in this case we always suggest to use export to CSV because we control the streaming and never run out of memory on the export type. Is there a specific reason export to CSV cannot be used? If you are using a template, then of course you can’t. But basic large data dumps do better with CSV.

I will use CSV for this one, since it does not have a template. I do have a report that loads by the EOY around 900K that does have a template and I was going to attempt to create this in TDP so I do not have multiple tools I am using. Thanks for investigating this.

Scott,

We made some improvement on memory handling and we would like you try out latest beta to see if it works for your issue. Here’s the download link:

www.toadworld.com/…/22155.aspx

Please let us know if it helps.

Thanks!

Kiki

Tried running with the new Beta and I am still receiving the Out of Memory error.

Sorry to hear this. Unfortunately we do not see anything else we can do at this time. Work around is to export to CSV. In the future we are trying to get our third party control to stream the data in chunks so we wont’ run out of memory. Right now everything has to go into memory before being written to Excel. We wrote the streaming ourselves in the csv export. that is why that type of export works.