There are many things that can be done to improve report performance, so this tip is the first of several.
Recently I was asked to review a completed report to see what could be done to improve its performance. Run time was over five minutes and this was unacceptable. The underlying database was Oracle OLTP, so the report had to perform several joins outside of the Package.
The report was also quite complicated, with the main page having a list, a crosstab and another list used as a summary. There were prompts on a prompt page and a second page that summarized the report. A whole lot going on.
The first thing I did was run the report for a small set of data and exported the result to Excel, naming it Page Layout Analysis.xls. Next I ran the final report query for the same prompt selections as a tabular view and exported the result to another Excel file, naming it Query Analysis.xls. With these two files open, and the report open in Report Studio, I was ready to dig into the report.
First, I mapped every element on the Page Layout Analysis to a query element. In Excel I noted what the underlying column name was in the final query. At the same time, I noted what elements were grouped, sorted and/or group spanned on the report layout, noting them in the Page Layout Analysis.xls file.
Next, working with the Query Analysis, I noted which columns were used in the final layout and which were not. Here is a summary of my findings:
- Out of 101 columns in the final query, only 27 were displayed on the page
- The final report had 12 levels of grouping and three additional sort elements defined
I know that when building a complex report, especially if there need to be in-report joins or if the specification is a moving target, that you end up bringing more out of the Package than would be necessary otherwise. There may also be elements added to ensure that joins are correct and to debug calculations, etc. After all, it's a pain to re-introduce additional elements through multiple levels of joins in the report later (wouldn't this be a great area for an enhancement?). But once a report is complete and correct (not always the same point in time), the author needs to clean out the unneeded elements.
Also, this report was of employees by organization - hardly something that would need 12 levels of grouping. Group Span should be used to ensure that values don't repeat, since it has a formatting function and no actual query processing.
My recommendations were to remove all elements from the final query that were not needed to produce the final report, as well as to replace as many group elements with group span. These changes were made very easily and the results were that the report ran in approximately 20 seconds.
Keep your report queries lean and groupings to a minimum - the extra work the server must do to carry extra columns around will slow down your reports.
