One of my favorite Maximo/BIRT sites is Bruno Portaluri's. He's an IBM IT Architect and has some of the best details related to Maximo customization and development. One his posts that is resonating with me right now is how to analyze report performance. A lot of time and effort is made by Maximo administrators to get the Maximo environment to run as quickly and crisply as possible. But what experience does an end user get when the application is snappy, but it takes several minutes to run a report? Here are some ways to look at your BIRT performance and speed up your trouble makers.

Getting the data

In Bruno's post, he took a look at how to get data from Maximo on your BIRT report performance. Maximo has a table called REPORTUSAGELOG and that data is generated by a cron task. Unless you, or your Maximo administrator, has changed the cron task setting you will have 30 days worth of data on the REPORTUSAGELOG table. The query that Bruno used to collect the BIRT performance is:

SELECT AVG(enddate-startdate)*24*60, reportname
FROM reportusagelog
GROUP BY reportname
ORDER BY AVG(enddate-startdate) DESC

If you happen to use SQL Server for your Maximo environment, you'll find this query will end up with the following error:

Msg 8117, Level 16, State 1, Line 1<br>
Operand data type datetime is invalid for avg operator.

The problem is SQL Server cannot make the datatype transition on the ENDDATE and STARTDATE fields. An updated query to run on SQL Server would be:

select avg(((convert(float, enddate))-(convert (float, startdate)))24.060) as avgrun, reportname
from reportusagelog
group by reportname
order  by avgrun desc

Now what was pretty amazing was how well my data matched what Bruno had originally posted. A graph of my data is shown below.

Take some time to look at what reports are sitting on the left side of that graph. Are those reports ones that users typically run from inside Maximo (PO Print, WO Print, etc) or are they typically scheduled reports delivered via email? Focus on reports users will run themselves and need to wait for completion.

Tips for Performance

Once you get an idea on what reports require attention, here are three things that can help make your reports run faster.

1. Use of IN vs EXISTS in BIRT: One of the quickest, but at times trickiest, ways to speed up BIRT reports is using the EXISTS statement instead of the IN statement in your queries. For example, listed below are two queries to check for Assets that may have Obsolete inventory listed on the Spare Parts list. One uses the EXISTS and one uses the IN SQL statement.

   (siteid = (select defsite from maxuser where personid = :USER) and status='OPERATING' and exists(select 1 from sparepart where assetnum=asset.assetnum and siteid=asset.siteid and itemnum in (select itemnum from inventory where itemnum=sparepart.itemnum and itemsetid=sparepart.itemsetid and siteid=sparepart.siteid and status='OBSOLETE')))

   (siteid = (select defsite from maxuser where personid = :USER) and status='OPERATING' and exists(select 1 from sparepart where assetnum=asset.assetnum and siteid=asset.siteid and exists (select 1 from inventory where  itemnum=sparepart.itemnum and itemsetid=sparepart.itemsetid and siteid=sparepart.siteid and status='OBSOLETE')))

   While each query will get the same information, the query that uses the EXISTS statement will run faster. Why? Essentially when you run a SQL query and use the EXISTS statement, once the database engine finds a hit to the condition in the EXISTS statement it will stop searching because the items does exist. Using the IN statement requires the query to first search, return the entire sub-query, and then do a comparison. This can cause a tremendous strain on your database if this happens to be against a huge table like Item Master, Work Order, Purchase Order, etc.

2. Reduce the data the report needs to crunch: In BIRT there are several ways to suppress data from showing up on the final report output. But the easiest way is to never have the data show up in the first place. This may seem like an obvious idea, but sometimes the conditional checks needed to suppress the data in the your report's SQL query can be tricky.

   The SQL query below is part of a Manpower Utilization check straight from BIRT.

   sqlText = "SELECT person.locationsite, labor.laborcode, laborcraftrate.craft, "
   + " labor.orgid, person.displayname, labor.personid "
   + " FROM   labor "
   + " JOIN person ON person.personid=labor.personid "
   + " LEFT OUTER JOIN laborcraftrate ON (labor.laborcode=laborcraftrate.laborcode) AND (labor.orgid=laborcraftrate.orgid) "
   + " where labor.worksite = ? and laborcraftrate.defaultcraft=1 "
   + " AND laborcraftrate.craft in ('AUTOTECH','ELEC','GROUNDS','MECH') "
   + " AND labor.status='ACTIVE' "
   + " AND  LABOR.LABORCODE NOT IN ('AUTO','GRNDS01','MECH','MECHANIC') "
   + " AND EXISTS(SELECT 1 FROM  labtrans WHERE  (labtrans.startdate >= dateadd(day, -13, convert(varchar, getdate(), 101)) AND labtrans.startdate < dateadd(day, -6, convert(varchar, getdate(), 101)) ) AND labor.laborcode=labtrans.laborcode) and " + where  
   + " ORDER BY labor.laborcode "

   The original report used some conditional suppression statements inside BIRT to hide Labor records that had NULL values for labor hours within the reporting period. This caused the report to chunk on handling these exceptions.With the updated EXISTS check, the report ran on average 65% faster.

   Take the time to work through ways you can reduce what information is pulled in by BIRT.

3. Keep the frills down: One of the traps early report designers (including myself) can get into is using all the bells and whistles available in the Report Designer. Keep the reports down to the barest essentials, but still be useful and informative for the intended audience. If just a list and summary will do, then don't add a graph. Minimize the use of pictures or other external data, this will keep report generation times down and reduce what needs to be added to the generated report.