Sometimes I had the hard task to debug performance issues of specific Maximo reports. The main thing to understand here is that 95% of the times the problem is related to a long execution time of the underlying SQL query .
What I typically do is the following:
- Look at the 'Performance' tab of the Report Administration application and take note of the 'Last Run Duration'.
- Configure an Eclipse Birt Designer environment to run the reports against the production database.
- Import the report into Eclipse.
- Change the rptdesign to log in a local file: mxReportScriptContext.setDefaultLogFile("D:/birtreport.log");
- Add a row to print the SQL query into the log file: mxReportScriptContext.getReportScriptLogger().debug(">>> sqlText=" + where);
- Run the report within Eclipse.
- Copy and paste the SQL query from the birt log file into a SQL client and try to execute the query straight to the database.
After this you have many elements to understand what may be the problem. First you need to find the cause performance issue and then work out the solution. Here are some examples:
- Long runng SQL query: The problem is in the database query or poor database server performance. Generate the query access plan and analyze the results. You can typically solve this issue creating one or two appropriate indexes. Here you may need excellent SQL skills. Do not improvise. Shout for some help from an SQL techie.
- SQL performances are good but Maximo is slow generating the report. This may be caused by a performance problem on the application server or a slow nettwork connection.
- One typical problem that I have seen many times is the HTTP timeout. If the report doesn't appear in the viewer but you see a reasonable execution time in Report Administration than you have to tweak your HTTP server configuration.
I wish you a merry debugging :-)
Labels: advanced, birt, database, performance, report, sql