iReport performance issue on Oracle

[SeanDay]

I have an odd performance problem with an iReport, when I run the query directly on Oracle in sql the results are returned in < 1 second. When I run it in iReports it takes about 8 minutes.. I have found that if I remove one of the LIKE clauses from the SQL below (making it an equals) then the iReport runs in a couple of seconds. It does not appear to matter which LIKE I remove. Has anyone seen anything similar before?

 I have run out of ideas as to what may be causing this..

Thanks,

Sean

Code:

SELECT DISTINCT stu.stu_code,                stu.stu_fnm1,                 stu.stu_surn,                 stu.stu_cad1 ||' '|| stu.stu_cad2 ||' '|| stu.stu_cad4 CONTACT_ADDRESS,                stu.stu_capc,                awf.dwh_admin_pwy_jn subject_code, pwy.pwy_name,                awf.attendance_mode, awf.enrolment_status,		decode(substr(awf.fee_status,1,1),'H','Home','O','Overseas','E','European', substr(awf.fee_status,1,1)) fee_status,                 amsl.description, amsl.study_location_key           FROM attnd_mon_students mon,                attendance_monitor_wkly_fact awf,                 attnd_mon_study_location amsl,		ins_stu@delta_link.uel.ac.uk stu,	        ins_pwy@delta_link.uel.ac.uk pwy          WHERE EXISTS (SELECT student_code                          FROM attnd_rep_wkly_3non_attnd awn                         WHERE awn.student_code = awf.student_code)	    AND awf.xtrct_wk_dt = (SELECT MAX(xtrct_wk_dt) from attendance_monitor_wkly_fact)  	    AND (mon.dwh_admin_pwy_jn like $P{PATHWAY_CODE_P})            AND (awf.attendance_mode LIKE $P{MODE_P})            AND awf.academic_year = $P{ACADEMIC_YEAR_P}            AND awf.school_code LIKE $P{SCHOOL_P}	    AND awf.occurrence_code LIKE $P{OCCURRENCE_P}            AND amsl.study_location_key LIKE $P{STUDY_LOCATION_P}	    AND SUBSTR(awf.fee_status,1,1) LIKE $P{FEE_STATUS_P}            AND amsl.join_key = awf.study_location_dkey	    AND awf.dwh_admin_pwy_jn = pwy.pwy_code	    AND awf.study_yr_dkey LIKE decode($P{BLOCK_P},'%','%',to_char(to_number('1') + 1))            AND stu.stu_code = mon.student_code            AND awf.student_code = stu.stu_code       ORDER BY 5, 1

[mdahlman]

Sean,

I assume by "directly on Oracle" you mean via SQL*Plus or maybe Toad. One thing you could try would be to run the same query via another JDBC tool. (I recommend SQuirreL.) That would let you confirm if the issue is related to iReport or to JDBC. I predict that it's probably a general JDBC issue.

Armed with that knowledge... I'm not sure exactly what you could do. ;-)
But if it's really an iReport issue, then there is presumably something that can be tweeked in iReport. If it's a JDBC issue, then perhaps there are other Oracle/JDBC resources that could help shed light on the problem.

Regards,
Matt

[SeanDay]

Thanks for the suggestion, I was running the query in both Toad and SQL Plus.

I have downloaded SQuirreL and using the same jdbc driver as I use for iReport the query works and returns the data in less than 2 seconds. Unfortunately this looks like an iReport/Jasper Report issue.

One thing I noticed when capturing the bind variables in Oracle was that iReport appeared to run the query twice (once using the default values setup on the parameters then a second time using the values I typed in). I cannot see what else is going on in the DB though as the query passed to Oracle looks the same the execution plan is the same and bind variables look OK.

I removed the default values from the parameters and the query only runs once but still takes about 8 minutes..

This is very odd behaviour but I have seen it before with another iReport we just removed one of the like clauses (making it an equal) and the query was OK. Fortunately it did not require a like in that case.

At least I have discovered SQuirreL which looks pretty impressive.

Sean

 

[bigboy1122]

Are you running the report directly from IReports?  Or through a JAVA JDBC program?

 

I was wondering if you were using log4J and turned debugging on if you could see how long the actual query was taking to run.  We have found its not the query thats taking a long time to run but the actual rendering of the report.  The query ran fast, just filling out the reports and subreports was taking a long time.

[SeanDay]

We have run the report both in iReports and on Jasper Server and both are slow, we think it may to be linked to the fact the query uses a database link.

As we only wanted to provide the users with an option of one or all in the parameter first we re-wrote the likes from:

WHERE  value LIKE param

to:

WHERE (param = '%' OR value = param)

However, although this appeared to fix the problem on iReports when we uploaded to Jasper Server the query started running very slow again.

I ran a trace on the Oracle sessions and the issue appears to be related to the database links (specifically the "SQL*Net message to dblink" and "SQL*Net message from dblink" events.  When the query is run in SQL Plus/Toad the session had 834 waits a total of 0.55 seconds. When run in iReports/Jasper the session had 1556893 waits a total of 508.29 seconds...

We then created a view in the database that looked at the database link and put this view into the iReport query and the query now appears to be fine on both iReports and Jasper Server..

Never really got to the bottom of what was happening but we have a solution that works.

Thanks,

Sean