Report runs slowly through Maximo

Hello everybody! We are using BIRT 2.1.2 and Maximo 6.2.7, running on Windows Server 2003 SP2. We have an issue with a report that I've not had any luck troubleshooting. When I run the report through the Eclipse preview window, it finishes in about 50-60 seconds. However, when I try running it through Maximo with the same parameters, it takes about 7.5 minutes just for the SQL queries to finish and the report never actually loads into the report window. I was able to see the report load once, but it took about another 8 minutes after the queries were finished before it did so. It usually just sits there indefinitely with the spinning blue circle of doom. We have tried running Update Statistics through Maximo. We already have indexes set up. We've tried deleting the log files as sometimes they get too big and that slows us down. I've tried optimizing my SQL queries. I am running out of ideas here and what we have left doesn't sound promising. So I guess I have two questions. First, has anybody dealt with this particular kind of situation and if so how did you resolve it? Two, can running too many update statements cause a slow-down like this? I'm wondering if I need to set up my report so that all the updates in the first section are executed at the same time. They're currently updating on each record. I'm pretty sure this is how it was done in the SQR version of this report, which ran just fine, so I'm not sure that this is the problem. Any input would be greatly appreciated, we're just about out of ideas here and we need to get this report fixed. Thanks!Chris

Find more posts tagged with

Comments

micajblock

Are you running SQL update statements in the report? I have heard others doing this also, but never understood why one would use a report for this purpose. Can you explain what the report is doing?

chclarke

Yes, we are running update statements. There are a few select statements that run first to grab the data we need, we store that data temporarily in variables (I use persistent global variables as I've had nothing but trouble with input/output parameters in this version of BIRT), then it updates the average daily usage based on some calculation. This is what the first section of the report does and is also the section that takes the longest, by far, to run. We have several other reports that run updates and inserts with no problem, but this particular report involves a much larger result set.

wwilliams

MBlock,Sometimes it is easier, (I am guilty as well) Maximo comes with the MIF <a data-ipb='nomediaparse' href='http://www-304.ibm.com/support/docview.wss?uid=swg21380484&aid=1'>http://www-304.ibm.com/support/docview.wss?uid=swg21380484&aid=1</a> which would be the preferred means in most cases, but even IBM supplies a report that updatesrolls up costs on assets.I am sure they have a reason as they have been doing it that way since Maximo was bundled with SQR <a data-ipb='nomediaparse' href='https://en.wikipedia.org/wiki/SQR'>https://en.wikipedia.org/wiki/SQR</a>If BIRT is what you know then maybe consider writing an xml or csv file and let the interface process the data. In addition, couldn't you create a view with your calculations and then create a "job" that inserts results to an interface table for the MIF to process?This could all be scheduled in the off hours.In the case of version 6.2.8, then use the MEA

micajblock

wwiliams,Do you know the name of the report example that IBM supplies with Maximo? I am curious as to how they do it.

wwilliams

mblock, why of course

it is the asset_costrollup_update.rptdesign.It might be so the user can visually verify the data prior to updating the db.-W

chclarke

Sadly, still no luck on this. I am currently re-creating the report with the line of thinking that there might be some artifacts in the previous copy that are causing problems. I doubt this is the issue since it works in Eclipse, but it's worth a shot. I have had those kinds of issues in the past and the version of BIRT we're using is rather buggy. Anyway, I'm waiting on word back from our paid software support. If they happen to figure out the cause of the problem, I'll be sure to report back here for anybody else that may run into this issue. Still open to ideas if anybody else has them. Thanks!

chclarke

Unfortunately, paid support was unable to come through for us (so far). They had a few suggestions, which I tried, but nothing works. I re-built the entire report, this time using input parameters instead of global variables for passing data between datasets, but the issue is still occurring. I was hoping that maybe the global variables were causing the web viewer to hang. I think I managed to shave thirty seconds off the SQL processing time, but the report still never resolves. There are no errors generated in the logfile or Eclipse. I think because it's such a large dataset, and because it runs slower through Maximo, that perhaps the webviewer or some other component is timing out before the report can finish loading. I've been told that the old SQR version of the report from Maximo 4 essentially took the same amount of time to process when run through Maximo, but they never had an issue with the report not loading. Is there a timeout setting anywhere for Maximo BIRT reports? For my most recent test of the report, here are important timestamps from the logfile: 10:44:40 - Running report csinv06.rptdesign for Maximo user <a data-ipb='nomediaparse' href='mailto:maxadmin@x.x.x.x'>maxadmin@x.x.x.x</a>10:51:25 - [print_reservations] close called. <--- this is the last dataset, so essentially this is the end of the SQL processing.10:59:26 - connection CLOSED10:59:26 - Successfully ran report csinv06.rptdesign for Maximo user <a data-ipb='nomediaparse' href='mailto:maxadmin@x.x.x.x'>maxadmin@x.x.x.x</a> It's almost 11:30 and the report still has not loaded into the web viewer window; all I see is the spinning blue circle of doom. I am at my wit's end. Has anybody else ever come across this before? If so, how did you resolve it? Does anybody have any ideas or suggestions? I'm all ears. Any help at all would be greatly appreciated. I will try to provide whatever additional information I can. Thanks!

micajblock

It probably has to do with the Maximo scripted classes work. They are probably not designed to perform large data loads. I would start thinking of re-architecting this so that the load of the data is not done via a report, but via a Java program.

chclarke

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="123366" data-time="1389722098"><div>It probably has to do with the Maximo scripted classes work. They are probably not designed to perform large data loads. I would start thinking of re-architecting this so that the load of the data is not done via a report, but via a Java program.</div></blockquote> Can you please expand on this? Or link me to something that will help me understand this better? Also, do you know why the report would work in Maximo 4 but not Maximo 6? Things are supposed to get better when you upgrade

At least if we have to go this route, it's pretty crystal clear which portion of the report would be done via the Java program! Thanks for your reply

micajblock

<blockquote class="ipsBlockquote" data-author="chclarke" data-cid="123367" data-time="1389722878"><div> Also, do you know why the report would work in Maximo 4 but not Maximo 6? Things are supposed to get better when you upgrade

</div></blockquote>You are going to have to ask IBM that. As far as pointing you in the right direction, other than maybe using MIF as wwilliams mentioned above, I have no idea without understanding the complete process.

chclarke

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="123368" data-time="1389723517"><div>You are going to have to ask IBM that. As far as pointing you in the right direction, other than maybe using MIF as wwilliams mentioned above, I have no idea without understanding the complete process. </div></blockquote>Thanks for your help mblock

I will get with the rest of my team and see what we can figure out on this. I agree that the report shouldn't be running so many queries that aren't even used in the report itself, it seems silly. But, that's how it was built in SQR, so I figured porting it over would work just fine. I'll post back here once we get something figured out.

chclarke

So, we have another development server that they just finished patching today. It's running Maximo 6.2.8 (we are currently on 6.2.7) and it uses an Exadata DB. I was asked to try the report in there and, so far, it's worked three out of three times! The processing time is about the same, maybe thirty seconds faster, but as soon as "Succesfully ran report ..." pops up in the logfile, the report starts loading in the web viewer. I'm not sure what actually resolved the issue, but in case anybody else runs into something similar hopefully this will give them another direction to try. Thanks for all the replies.