Page 127 - ASBIRES-2017_Preceedings
P. 127
IMPROVING THE PERFORMANCE IN REPORT GENERATION PROCESS
ODBC drivers available for a given type of The use of IN clause in filtering data
database, and some are better than others. ranges were replaced with maximum and
The only way to really test this is to run the minimum dates
report with all the suitable ODBC drivers IF-ELSE clauses are replaced with basic
and see which is the most efficient (Dove, logic such as OR where it is possible
2010)
Uses of crystal functions such as ToText,
3 METHODOLOGY CDate that SQL do not understand were
reduced whenever possible.
During the research investigations it Filtering was updated in a way that record
was found that many applicable solutions selection could be done in a SQL friendly
are available to resolve the problems way, to make the database return only the
encountered by Report Manager tool out of right set of data needed
which some were already implemented. Filtering was written to run in the database
Among the feasible solutions, followings are
identified as effective. end as much as possible to avoid it
bringing all the unwanted data and avoid
Enhancements from Database the filtering done in the report generation
Selecting the suitable OJDBC driver at the client side
Removing sub reports The queries were investigated and
Enhancements in filtering of data replaced with suitable solutions
Considering the existing resources and the considering where they will be executed,
requirement, the solution of filtering is Client or Server side (e.g. IF ELSE
selected as the effective solution. Many executes in client side Therefore, it is
causes for low performance in filtering replaced with OR)
were identified and the following steps Use of dependencies in between formulas
were taken to overcome them. were reduced and column names were
directly used in formulas
A list of slow performing reports was The filtering criteria were ordered
created and was used as reference material according to the criticalness of the filter.
Those reports were run individually using For example, the date band was checked at
Report Manager by prescribing search first so that the majority of the records will
criteria based on a time period, and the be filtered from that and there will be
time gap between the request and response fewer records to check for the rest of the
were noted. Here, the human error was criteria.
considered to be null as the reaction time There is almost no difference between
gap of starting the timer by clicking search performances of if/else and switch
button is considerably equal to the time operations, but switch was more effective
gap for the reaction of stopping the timer. when there are more than five or six
Reports causing a considerable delay conditions to check. Therefore, where
(more than 10 minutes to load data of one there are more than five records, if/else
year or five minutes for the data of six was replaced with switch since it is easier
months.) were selected. These criteria to access the last conditions
could differ according to the amount of All the null filters which were checked for
results fetched from the report which can null values were removed where there was
be analysed by the rows of result from no possibility of the report’s search criteria
running the database query in SQL to return a null value on that occurrence
developer, or the number of pages Reports were uploaded and tested after the
generated by the report. changes were made and then a comparison
Changes for the filtering part were applied was done between response times before
as a hard coded program. Measures taken in and after applying changes
this step are as follows:
117