February 10, 2012

Troubleshooting Birt report performances

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:
  1. Look at the 'Performance' tab of the Report Administration application and take note of the 'Last Run Duration'.
  2. Configure an Eclipse Birt Designer environment to run the reports against the production database.
  3. Import the report into Eclipse.
  4. Change the rptdesign to log in a local file: mxReportScriptContext.setDefaultLogFile("D:/birtreport.log");
  5. Add a row to print the SQL query into the log file: mxReportScriptContext.getReportScriptLogger().debug(">>> sqlText=" + where);
  6. Run the report within Eclipse.
  7. 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  :-)

9 comments:

  1. Hi Bruno...
    Thanks a lot for your tips.
    I have a question, how we determine that our query statement are so bad for a complex query execution within a thousands record from some tables that need to examined?

    ReplyDelete
  2. Oh.. one more question., I can not see "performance" tab on report administration, what maximo version you use?

    ReplyDelete
  3. I'm running on Base Services 7.1.1.6. I have an old MAM 6.1 server and I don't see this tab. I do not now in which version this tab has appeared.
    Regarding the evaluation of the effective performance my answer is... depends.
    From a user perspective it is not acceptable to wait more than 1 minute for an online report.
    As a general rule I would say that 'thousands' are not many rows for a modern RDBMS. Millions probably would be.
    Another important aspect is how many rows are fetched from the database thus how long is the generated report. Few hundreds of pages should be handled without any problem.
    The explain plan is the main guidance here. If you see large table scans when joining two or more tables that is the right place to try some indexing.

    ReplyDelete
  4. Hello Bruno,

    We are trying to implement Maximo 7.5 using WebSphere 7.0.0.14 and BIRT for reporting. We are experiencing HTTP timeout where the report doesn't appear in the viewer but I see an execution time in the Report Administration of over 1 minute. I noticed that the timeout will occur if the report execution exceeds one minute. How do we go about tweaking our HTTP server configuration?

    ReplyDelete
    Replies
    1. It may be in different places depending on what HTTP server are you using and the configuration of your cluster.
      I'm not a system administrator so I'm not able to support you on this problem.

      Delete
  5. I'm having the exact same issue as Jason P. Have you or anyone found out where to tweak the HTTP server timeout in Maximo 7.5 with WebSphere?

    ReplyDelete
  6. Please try this: http://www-01.ibm.com/support/docview.wss?uid=swg21607119

    ReplyDelete
  7. Hi Brunco,
    Iam running BIRT 371 report and it is running fine on BIRT Designer but when same report iam running on MAXIMO 7.5.0.4 then it is taking long time to run.
    Ex: BIRT Designer taking1 min for more than 5000 records.
    MAXIMO taking 45 min for more than 5000 records and some time got blank.

    If i restart maximo server then from maximo report took only 1 min .

    Any idea??

    Thanks

    ReplyDelete
    Replies
    1. Did you got this resolved ..can you share whats problem area and fix applied ?

      Delete