Page 127 - ASBIRES-2017_Preceedings

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