Extract SQL Query Results to 'xlsx' bogs down at 150,000 rows
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