Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am developing a web application in Java/J2EE, in which I have export functionality. I have used Jasper for the same. The issue is when trying to export a huge set of data it is consuming a lot of time.

Is there any design approach for export functionality so that I can improve the performance?

I had tried JExcel API earlier and then moved to DynamicJasper API.

share|improve this question
5  
Please define "huge" and "a lot of time". –  Doc Brown Mar 2 '12 at 12:38
    
For exporting a 25,000 rows and 12 columns it is taking 7 minutes. –  Hari Mar 2 '12 at 13:51
    
@Hari Is the bottleneck in downloading the file or in generating the file? If the bottleneck is generating the file then Darknight's response is appropriate. You might want to also see if the database queries that retrieve the data for the Excel report can be tuned. Often the biggest performance hit is joining tables and retrieving the data from the database. If these queries can be improved or perhaps indexes and statistics can be improved by the DBA, then you could notice a dramatic increase in performance. –  maple_shaft Mar 2 '12 at 14:20
    
@maple_shaft the bottleneck as of now is in file preparation which is taking the 75% of the time. on the query side it is a simple query and there is no joins on the query. But as you had mentioned we will have a look in the indexes. –  Hari Mar 2 '12 at 14:31
    
Profile! Find the bottleneck. –  user1249 Mar 6 '12 at 21:32
show 1 more comment

3 Answers 3

They way I've approached a similar issue was by creating a separate service.

Requests for excel data are placed in a queue.

The service then processes the requests and returns the excel as an email attachment, the return emails are specified as part of the initial request.

This has worked flawlessly for us, and when things crashes (I've only every had two instances), the requests are not lost, they simply queue up.

share|improve this answer
    
There are size limitations to email transmission, but if you are confident in this then it is a good way to keep from chewing up your app server. –  maple_shaft Mar 2 '12 at 13:16
1  
its well managed, the service apps, run on service app servers. If they crash then they can be rebooted. In our case this has never happened. Your mileage may very. Has the service application crashed? yes twice (for different reasons). But a nice logging feature and alert is built in –  Darknight Mar 2 '12 at 14:08
    
Neat, another option that is similar is for the web app to submit the Excel generation request and then create a user specific token that will allow the user to retrieve the generated file from the web app at a later time. The download url with token can be emailed out. Another service or daemon can run that cleans up the temporary files from the filesystem or database after they have expired. –  maple_shaft Mar 2 '12 at 14:17
    
I've used the same approach more than once. Back in the "old days" of VB6 and Classic ASP, I even did this with MSMQ and COM+. –  jfrankcarr Mar 2 '12 at 15:00
add comment

We do that using Apache POI, but since Jasper probably uses that too, I don't think you will get much faster results by directly using POI.

Large exports (like 50000 rows x 15 columns) require a lot of memory, so increasing the heap space is a must, but you have probably already figured that out.

You might want to give JExcelAPI a try. I have no experience with it, though.

share|improve this answer
    
we had already tried with JExcelAPI, after that choose Jasper. For the same set of data (50000 rows *12 columns) in Jasper it is taking almost 20 minutes to download and the heap size is almost 1.5 GB –  Hari Mar 2 '12 at 14:23
add comment

You might try exporting to .csv, which Excel will happily read. Thats how we do all our 'excel' exports in our web app. Its very easy to output, and you dont need a library for it (though there probably is one for Java). Plus its human readable.

7 minutes for 25,000 rows sounds extraordinarily slow. I'm talking would have to go out of your way to make it that slow slow. You might do some metrics to see how the time differs between different sized datasets. If you see it getting progressively slower, it may be an indication the exporter is doing something like loading the excel data, adding a row, saving to file... loading the excel file, adding another row, saving to file, etc.

share|improve this answer
    
Actually we are providing different file format' to be downloaded, in that we are providing CSV also. As you and thorbjorn have mentioned i will use a profiler to check the bottle neck. For 5000 records and 12 colum it is just taking a minute or less, as the data increase the time increases exponentially –  Hari Mar 12 '12 at 8:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.