Export a large number of records (100,000+) to excel from OOTB report browser
We have a requirement to export a large number of records to excel (100,000+).
We are using PEGA 7.2.0 and office 2010 with IE browser
Currently to get the data we are using the OOTB report browser and a report definition. The initial limit is set to 10,000 rows and if the export to excel option is selected from the options it works in an acceptable amount of time, however this limitation isn't acceptable to the business. If the limit is increased in the report definition then very quickly the performance becomes unacceptable. Once the limit is set to 50,000 we are looking at a time of about 15 minutes. There appears to be several factors to this time.
We have looked at the SQL query time and this is between 4 to 7 seconds.
1. The amount of time taken before the option to open, or save the file is about 7 minutes.
2. Once saved then the amount of time taken to open the file by excel is about 7 minutes.
The file size produced is very large (roughly 75 MB). If this is opened in excel and resaved as an excel file then it is about 4.5MB.
Longer term Pega will not be used as the Strategic supporting solution but as of now it is the only option we have, we do not have the option yet of using something like BIX.
So I have the following questions:
1. Is it possible to generate the file in the excel format initially (so it is 4.5MB)? We do not have any requirement to use chrome or Safari. As I understand it the file generated is effectively HTML and it is done this way for X browser compatibility. Since we do not have this limitation can we exploit this somehow?
2. Is there another way of increasing the performance?