Crystal Reports: ways to enhance the performance?

[former_member548403]

Hello.

I have a report that I utilize the "IF THEN" function in the command section and I think it is causing the performance to lag. For example, below is like 1/5 of the entire SQL in the command section:

if ('{?pgmid}' like'1' + '%') and '{?geotype}' = 'station_response_area'
begin
select incident_num, 
unit, 
incident_date, 
station_response_area geotype, 
measure_time,
CONVERT(CHAR(8),DATEADD(second,measure_time,0),108) as Response_Time,
pgmid, 
NFPAStandard,
responsecategory,
meetstandardcnt,
cast(measure_time as varchar) as time_sec,
pgmdorder,
incident_type,
property_use
from vw_nfpa_firstArv_RPT
where incident_date between '{?BeginDate}' and '{?EndDate}'
and pgmid = {?PgmID}
order by measure_time asc
end

if (('{?pgmid}' like '2' + '%')  or ('{?pgmid}' like '3' + '%')) and '{?geotype}' = 'station_response_area'
begin
select incident_num, 
unit, 
incident_date, 
station_response_area geotype, 
measure_time,
CONVERT(CHAR(8),DATEADD(second,measure_time,0),108) as Response_Time,
pgmid, 
NFPAStandard,
responsecategory,
meetstandardcnt,
cast(measure_time as varchar) as time_sec,
pgmdorder,
incident_type,
property_use
from [DW_PROD].[dbo].[vw_nfpa_ERF_RPT]
where incident_date between '{?BeginDate}' and '{?EndDate}'
and pgmid = {?PgmID}
order by measure_time asc
end

Would there be a way to substitute the "IF THEN" with something else to enhance the performance of the report?

[DellSC · Accepted solution]

The If..Then is absolutely slowing down the query.

I would try moving the criteria from the If..Then down to the where clause and then use "UNION ALL" to string the queries together. Something like this:

select 
  incident_num, 
  unit, 
  incident_date, 
  station_response_area geotype, 
  measure_time,
  CONVERT(CHAR(8),DATEADD(second,measure_time,0),108) as Response_Time,
  pgmid, 
  NFPAStandard,
  responsecategory,
  meetstandardcnt,
  cast(measure_time as varchar) as time_sec,
  pgmdorder,
  incident_type,
  property_use
from vw_nfpa_firstArv_RPT
where '{?pgmid}' like '1%') 
  and '{?geotype}' = 'station_response_area'
  and incident_date between '{?BeginDate}' and '{?EndDate}'
  and pgmid = {?PgmID}
order by measure_time asc


UNION ALL


select 
  incident_num, 
  unit, 
  incident_date, 
  station_response_area geotype, 
  measure_time,
  CONVERT(CHAR(8),DATEADD(second,measure_time,0),108) as Response_Time,
  pgmid, 
  NFPAStandard,
  responsecategory,
  meetstandardcnt,
  cast(measure_time as varchar) as time_sec,
  pgmdorder,
  incident_type,
  property_use
from [DW_PROD].[dbo].[vw_nfpa_ERF_RPT]
where ('{?pgmid}' like '2%')  or ('{?pgmid}' like '3%'))
  and '{?geotype}' = 'station_response_area'
  and incident_date between '{?BeginDate}' and '{?EndDate}'
  and pgmid = {?PgmID}
order by measure_time asc

By putting the parameter check first, the select won't actually do anything if the params aren't indicating that that part of the query should be run.

Also, you don't need to use "+ '%'", just put the "%" on the end of the value like I show above.

-Dell

[former_member548403 · Accepted solution]

Thanks so much!