I have a report that must satisfy several complex business rules. Because of this, we first need to have a view which conditionally populates columns from up to 3 different tables. Running the report involves running a controller.search() to find line items(lets call rows in the view line items) within a date range that satisfy a couple other parameters. The records are sorted in order to print out grouped and summarized by 3 fields. In addition to that, we need to report on beginning balances and ending balances. Ending balances are no problem since we can keep a running tally starting with the beginning balance then update it through each iteration of a line item as it is being processed to be written to a .pdf or .xls file. However beginning balances require that we query the view to get a sum up to the beginning date of the report for each grouping. When there are a few dozen groupings this report can take well over an hour to generate. Whether it is being written to .pdf or .xls does not make too much difference.
This report is currently being run using 3.5.10, interestingly enough it ran about 25% faster using 3.5.3, but was still intolerably slow. The back end database is MySQL on the same server as servoy. I was wondering if anyone had any ideas on how to make this run faster, or maybe alternative approaches or best practices? I think one of the best aspects of servoy is being able to run complex reports that can pull data from multiple tables and be formatted nicely using html or an array. I’d just like to learn how to run these kinds of reports more efficiently.
We will look into the possibility of Jasper reports for this to see if it can handle all the business rules, I’ve never used them before but constantly hear good things about them. One of the requirements is that we have the ability to create an excel spreadsheet which can be modified further by many users. David, as it turns out using the Sum function of SQL to get the beginning balance for each group is what takes the longest in this report. Iterating and updating a field tallying the total turns out to take negligible time compared to getting that initial balance, but I understand your general point of trying to minimize UI updates, and will try to streamline the report that way. I’m also trying to figure out a way to pre-process the beginning balances, but that will need to come in the form of some compromise with the users. Could using a MySQL View as opposed to a table for this report be adding to the time complexity of the queries at all?
By the way, we’ve used time stamps in the method to time each section of the report and the performance data log on the application server admin console which has been very valuable.
I tried doing that, and it took even longer, which was odd because I did replace x number of complex queries with 1 complex query. At that point though the query became so complex I could not say with certainty that it was written optimaly. I may revisit that approach again
This really is running several reports and appending each one to a document through the iterations, so I’m afraid there’s little options in bringing things outside the for loop.
I will look into Jasper reports and see what I can do with them. It looks like in the mean time we will be able to work out some archiving procedures with the users that will cut down on the size of the view and pre-process some of the info for the report.