3

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.

Improve this question
7
  • 5
    Please define "huge" and "a lot of time".
    – Doc Brown
    Mar 2, 2012 at 12:38
  • For exporting a 25,000 rows and 12 columns it is taking 7 minutes.
    – Hariharbalaji
    Mar 2, 2012 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, 2012 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.
    – Hariharbalaji
    Mar 2, 2012 at 14:31
  • 1
    -1. I think this question is too vague. There are endless things you can do to improve an applications performance. There is not enough information here to provide a useful answer.
    – Craige
    Mar 6, 2012 at 21:36

4 Answers 4

Reset to default
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.

Improve this answer
4
  • 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, 2012 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, 2012 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, 2012 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, 2012 at 15:00
2

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.

Improve this answer
1
  • 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
    – Hariharbalaji
    Mar 12, 2012 at 8:10
1

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.

Improve this answer
1
  • 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
    – Hariharbalaji
    Mar 2, 2012 at 14:23
-1

Try using this framework:

https://github.com/Inspiredsoft/exporter

it is based on annotation.

Improve this answer
1
  • 1
    This is a link-only answer. While the link could perhaps help, you should summarize in a couple of sentence what's behaind this link and why you think it's well suited for the question.
    – Christophe
    May 22, 2016 at 21:21

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