This content has been marked as final. Show 7 replies
You may have already seen one or more threads like the following on the issue of increased memory overhead for the Excel formats:
Re: Sql Developer 3.1 - Exporting a result set in xls generates and empty file
Basically SQL Developer uses a third-party API to read and write these Excel formats. There are distinct readers and formatters for each of the xls and xlsx forms.
There is a newer version of the API that supports streaming of xlsx Workbooks. Basically it achieves a much lower footprint by keeping in memory only rows that are within a sliding window, while the older, non-streaming version gives access to all rows in the document. The programmer may define the size of this window. I believe the newer API version was either not available or not stable during our 3.1 development cycle. Possibly a future SQL Developer version might use it.
SQL Developer Team
Thanks very much for your feedback. I must not have been patient enough with my searching to have missed those other threads about the XLS and XLSX extracting.
Any idea why the XLS seems to work fine for me but the XLSX export on the same data bogs down?
Is it just the difference between the how the API works for the different formats? It would make sense.
I can try to export as CSV and the user can do what they want with it. In this case it was a 1-time thing and the user manually merged the worksheets created using the XLS format.
Thanks again for your quick response.
Export your rows as .CSV. Excel will read the file just fine. Also it seems like when you export as .CSV, SQL Developer just pipes the rows thru from the database query directly to the file. As a result the amount of memory it (SQL Developer) consumes does not grow and there is not a limit to the number of rows that be sent to the file, aside from what Excel can handle later. Exporting this I have been able to move millions of rows pretty quickly. MS-Office 2007 and 2010 can read a .CSV file this big and convert it to .xlsx easily. Also if done this way the resulting .xlsx is much smaller than the .csv file.
Not really sure since it's a third-party API. One might guess XLSX format adds functionality that requires more memory than XLS.
Any idea why the XLS seems to work fine for me but the XLSX export on the same data bogs down? Is it just the difference between the how the API works for the different formats? It would make sense.
Anyway, in order to get the most out of the current export code, CSV is a better choice. And as Joyce notes in the post I reference above, whatever the export format, if all the result set rows for a query have not been returned, then requesting an export will re-execute the query consuming even more memory.
When you export with the .CSV format, SQL Developer does NOT consume more memory as the query is executed and the rows are fetched from the database. It appears that fetch set is piped or directed directly to the export file. This lack of memory consumption as the query executes can be confirmed in the MS-Windows environments in task manager while watching the amount of memory allocated to the SQL Developer VM.
You are correct, of course, and actually I was not contradicting you. I only referred to the case where the user has executed a query that returns thousands of rows via Run Statement and has been scrolling through them in the Query Results grid prior to requesting an export. In this case, the grid is a primary culprit when it comes to holding on to memory. That might be clearer if you read through the other forum thread I referenced in my post above.
If the user has not scrolled to the end of the result set (either manually or via Ctrl-PageDown or Ctrl-End), then that memory plus whatever memory export uses in it's processing (including re-executing the query) gets allocated in the Java VM. Ignoring memory held by the grid, export's memory consumption for the CSV format does seem constant during the course of the export regardless of the number of rows processed.