JasperReports Question (Speed up report generation)

[Steven Satelle]

Hi all, I'm working with a number of jasper reports ranging from 1.1 up to 3.1.x, most developed by others but I responsible for them now.

All the reports use Oracle (9i) databases as the datasource and generate the data from SQL queries

I have a problem with one of the reports being to slow, Each run takes between 5 to 10 minutes and I need to generate about 1000 of them quickly.

Now I know that the problem is not the report, it is the database. If I run the query directly against the database it takes just as long to run.



So, My question, is there any way in Jasper to speed this up, using hibernate or anything else? Or an I stuck with tuning the database?



Any advice appreciated :-)

**Note I'm guilty of that heinous crime of crossposting :!: I asked this on the JasperForge forums, but it seems really quiet, I got no responses

[Pawel Nowacki]

Maybe its just the case for query optimalization. You should write it and give table structures. Or tuning query its just what you want to avoid?

[Steven Satelle]

The query gathering the data is 40 lines long, but it uses views, when you get the code from the views, they have sub views. The total length of the query is over 300 lines.
I know that there is duplication of the gathered data in these views. I've already taken out most of the ltrim(rtrim()) stuff since there is no need to trim a single field 5 or 6 times.


I guess I'm looking (praying) for a quick fix through code. Otherwise I'm going to have to get more personal with the DB than I am with my wife

[Martijn Verburg]

I'm afraid that it's case of query optimization, looks like you'll have to get intimate with the Oracle DB and the queries running on it...

[Pawel Nowacki]

Maybe its too obvious, but apart from getting personal with query you could also take some admin steps. Try to set some indexes and apply partitioning on tables then if it won't help just write the whole query from scrach...

[Steven Satelle]

Yea, that would probably be a little quicker, but taking the (cursed) long view, the best option would be both, tear the query apart and rebuild it so I understand exactly what it is doing, and then (possibly) re-index the db, but for best effects I really need to know what indexes I want.

I hate when I take the long view, it always seems to be me doing more work

[Martijn Verburg]

Hi Steven,

Before ripping it totally apart, don't forget to look at the query plans first. As the prev poster stated it may just be the case of needing a simple index, I certainly hope so! (re-writing queries is never fun)

[Steven Satelle]

I've run a query plan, it gave me a score of 257, most of the points being gained in the inner joins :?

[Martijn Verburg]

Hi Steven,

You're using Oracle right? IIRC the query plan has an option that shows you the full sets of steps it takes and the weighting for each section that makes up the whole score. Is that the case with what you're getting (sorry just trying to remember back to my dark days with Oracle).

Can you post an example of one of the queries/plans? Maybe we can assist in finding some common quick wins for you. Nobody wants to see a fellow developer suffer through re-writing SQL!

[Steven Satelle]

Sorry for the delay in coming back but I've been working elsewhere for the last few days.

I have a partial explain plan down the end (sorry about the way it is formatted)

But, I've also got a meeting booked with an SQL developer next Monday to go through this. I'm going to "leverage" the power of our organisation, and get someone who is an expert at sql to show me exactly how to fix this

Operation Table Name Options Cost
View 164
Sort ModuleEntry Group By 164
Table Access By Index ROWID 154
Filter predicates -
Nested Loop 163
Merge Join Cartesian 10
Table Access Grade Full 3
Filter predicates -
Buffer Sort -
Table access Centre Full 8
Filter Predicates -

[Martijn Verburg]

Looks like there are a couple of possibilities there indeed (I understand that you probably can't show the SQL itself), let us know how you get on!