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
   122   123   124   125   126   127   128   129   130   131   132