Slow report crystal reports 8.5

[wmannod]

Hi,
I have a report that runs very slow. I have a formula in group header1. If I delete this formula the report is very quick.


If GroupName ({BDMSTP.BMCC}) = {GROUPD.GROUP#} then {GROUPD.DESC}
else 'MISSING'

It is working fine but there are only 7 groups and it reads 1800 records very slow. I would like this to be the last step of the report and read the group and attach the description. If I use the Case formula below the report is really quick, but there is the hard coding issue that I don't like.


select {BDMSTP.BMCC}

case '141':
"ARROW RELOAD LRC"
case '142':
"ARROW RELOAD TRUCKING"
case '143':
"NELSON RELOAD LRC"
case '144':
"THUNDER BAY RELOAD LRC"
case '145':
"VERMILLION BAY-RELOAD LRC"
case '146':
"VERMILLION BAY-RELOAD TRK"
case '147':
"SASKATOON - RELOAD LRC"
case '148':
"SASKATOON-RELOAD TRUCKING"


Default: "MISSING"

 

[lbass]

Group on {BDMSTP.BMCC} and then go to report->group expert->options->customize group name->use a formula to customize name->x+2 and enter:

if isnull({BDMSTP.BMCC}) or
trim({BDMSTP.BMCC}) = "" then "Missing" else
{GROUPD.DESC}

This assumes that you have added the GroupD table to the report and linked it with a left join FROM {BDMSTP.BMCC} TO
{GROUPD.GROUP3}.

-LB

 

[wmannod]

Your formula also works, but it gives me the same results as my formula only instead of printing BMCC then DESC in the group header, it prints desc and desc. But it has not improved on the speed of running the report.

My Version

IE: GH1
(textfield) (BMCC) (DESC)this is the formula I need to fix
Cost Center: 141 Arrow Reload LRC

Yours
Cost Center: Arrow Reload LRC Arrow Reload LRC

And yes I do have the link in.

 

[synapsevampire]

Odd...

Try forcing a whileprintingrecords, and make sure that this field is only in the group section:

whileprintingrecords;
select {BDMSTP.BMCC}
case '141':
"ARROW RELOAD LRC"
case '142':
"ARROW RELOAD TRUCKING"
case '143':
"NELSON RELOAD LRC"
case '144':
"THUNDER BAY RELOAD LRC"
case '145':
"VERMILLION BAY-RELOAD LRC"
case '146':
"VERMILLION BAY-RELOAD TRK"
case '147':
"SASKATOON - RELOAD LRC"
case '148':
"SASKATOON-RELOAD TRUCKING"
Default: "MISSING"

An alternative might be to create a SQL Expression to always return this for each row using the appropriate syntax for your database, but you didn't post your software version nor database, a common, but critical oversight.

-k

 

[lbass]

I meant for you to remove your formula, group only on BMCC, and use the customize group name formula area. I can't tell if this is what you did. My formula would only return one description in the group header.

-LB

 

[synapsevampire]

Sorry, misread that.

Doesn't make sense that it would be significantly slower.

Try:

whileprintingrecords;
If {BDMSTP.BMCC} = {GROUPD.GROUP#} then
{GROUPD.DESC}
else
'MISSING'

-k

 

[wmannod]

Hi LB,

Ok I have deleted my formula and only used yours....and yes it works but also over 1 minute to run report same as using my orginal formula. Where as using the Case option it runs in seconds. (but not the option I want to use)

And re: K
The software version is listed in my heading crystal reports 8.5. And I want a solution where the report will only go to my DB2 table and get the desc for BMCC on GH1 not have to read every record in the table. I hope this makes sense.

D

 

[wmannod]

Hi K,

Thanks, I tried the whileprinting and it didn't speed it up. I'll go home and sleep on it. Maybe a light bulb will go on, or it may work in the morming....

 

[synapsevampire]

I'm surprised that the speed is significantly different.

Again, a SQL Expression can return the value, and since you're returning ALL of the rows anyway (I assume this to be the case), then the performance hit should be minimal. I didn't notice the CR version, and I guess you don't care to do this on the database side or you'd take the time to post the type/connectivity used.

It could be that Crystal is passing the GROUP BY to the database esxcept when this formula is added, in which case you could add the other field in as a group and it should still perform a group by on the database.

You may be better served to use a View or SP if speed is the concern.

-k

 

[wmannod]

Thanks everyone for all your help. I striped down this report last night and removed all the links and redid them.

The report is now running faster, only slightly slower then the "Case" option.

Database was DB2, with ODBC connect to AS400 iseries, software version Crystal Reports 8.5.