Performance

Do not use named cells in an IBM® Controller function. Named cells make the update of the sheet slower.

Example: In a cell you enter the currency code and name the Currency cell on the Formulas tab/Defined Names group/Define Name menu (Excel 2007).

Reports containing many different combinations of parameters in rows and columns are most often slower than more simple reports. The performance of a report is optimized on the basis of the design, combinations of parameters in rows and columns, in a report. Many different designs make the optimization difficult.

The processing time of a report is, among other things, affected by the so called Used Range in an Excel sheet. The recommendation is that if you don't have a good reason to do the opposite, keep the Used Range as small as possible in a report.

Examples of situations to avoid where excessive ranges are created are described below:

If you insert a function in e.g. cell K1200 in an Excel sheet, the Used Range of that Excel sheet will be the area stretching from cell A1 to cell K1200.

If you select an entire column and format it in some way, e.g. paint the column D red, this implies that the Used Range will stretch from cell A1 to cell D65 536, in other words a very large area. The same Used Range will be produced if you Freeze Panes on column D.

If you select an entire row and format it in some way, e.g. paint the row 45 blue, this implies that the Used Range will stretch from cell A1 to cell IV45, a large area. The same Used Range will be produced if you Freeze Panes on row 45.

Note that the response time of a report is closely related to how many fGetVal functions it contains. A good rule to remember is that if you multiply the number of fGetVal functions in an already large report by two, this might increase the processing time by more than two. Try to minimize the number of fGetVal functions in a single report. If possible divide your reports so that you have one report for each purpose.

If the report gets too large and/or too complex, a drastic change in the response time occurs if the database fails to optimize the generated SQL statement.