Skip to Content

Crystal Reports: ways to enhance the performance?

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Oct 14, 2019 at 01:51 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.