7 Replies Latest reply: May 15, 2012 4:26 PM by Gary Graham-Oracle RSS

    Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows

    garywicke
      Environment:

      SQL*Developer 3.1.07.42 on Windows XP SP3
      Oracle 11.2.0.3 EE on Solaris 10.5

      I ran a query in a worksheet window and the first page of results came back in 10 seconds, whoo hooo!

      I right-clicked the first column in the first row and selected 'Count Rows' and it returned 527,563 after thinking a bit.

      I right-clicked 'Export', selected a format of 'xlsx', unchecked the box for 'Query Worksheet Name' and browsed to specify the output file directory (my local C: drive) and file name. I clicked 'Next' and then 'Finish'.

      I watch the row counter at the bottom right of the window and it went very fast until it hit about 150,000 rows and then it started slowing down. It got slower and slower and slower and slower, well you get the picture, and I finally killed the process when it took over 15 seconds to get from 156,245 to 156,250.

      Why would this be?

      Additional information:

      I ran the exact same query again and exported the same 527,563 rows using the 'xls' format instead of 'xlsx' and the process proceeded very quickly all the way to the end and completed successfully in just several minutes. The resultant spreadsheet contained eight (8) worksheets since it could only put 65536 rows into each worksheet. This was acceptable to the user who simply merged the data manually.

      Is there some issues with using 'xlsx' as the output format as opposed to just using it as an input format?

      Does SQL*Developer try to create a spreadsheet with as many rows as the data up to the max in Excel 2010 (which is more than 527,563)?

      Thanks very much for any light shed on this issue. If I've left out any important details please let me know and I'll try to include them.

      -gary
        • 1. Re: Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
          Gary Graham-Oracle
          Hi gary,

          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.

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
            garywicke
            Hi Gary

            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.

            -gary
            • 3. Re: Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
              ScottK
              Gary,

              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.

              ScottK
              • 4. Re: Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
                garywicke
                Hi Scott

                Thanks very much. I'll do it that way the next time it comes up.

                Thanks for the tip!

                -gary
                • 5. Re: Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
                  Gary Graham-Oracle
                  Hi gary,
                  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.
                  Not really sure since it's a third-party API. One might guess XLSX format adds functionality that requires more memory than XLS.

                  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.

                  Regards,
                  Gary
                  • 6. Re: Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
                    ScottK
                    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.
                    • 7. Re: Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
                      Gary Graham-Oracle
                      Hi Scott,

                      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.

                      Thanks,
                      Gary