Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Added Formula Causing Slow Report Post Reply Post New Topic
Author Message
blackfish
Newbie
Newbie


Joined: 08 Nov 2011
Online Status: Offline
Posts: 17
Quote blackfish Replybullet Topic: Added Formula Causing Slow Report
    Posted: 24 May 2016 at 11:49am
I have a subreport that has 18 separate formulas (mostly if then) that reference 18 separate queries in an Access db.  When I add an additional formula, the report time jumps from a few seconds to 15 minutes.  If I keep that formula and delete an existing formula, the processing time goes back to a few seconds.  In general, the formulas are quite simple.

I have tried the following to speed things up to no avail:
  • use variables to reference the db fields and then use the variables in the formulas
  • make the queries simpler by not have the default answers
  • switching between if-then to select-case statements
  • changing the inner joins to left outer joins
  •  rewrote the formulas to have the default answer come up first

Is there a limit as to how many formulas a subreport can have?

thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 May 2016 at 5:56am
generally when you see this jump in execution time it has to do with a join being enforced by the addition of the formula (not the actual formula itself).
When you added this new formula did it include a field form a table that was 'dormant'?
You can sometimes see this in action by just dragging a field from the table into the report

Edited by DBlank - 31 May 2016 at 5:57am
IP IP Logged
blackfish
Newbie
Newbie


Joined: 08 Nov 2011
Online Status: Offline
Posts: 17
Quote blackfish Replybullet Posted: 01 Jun 2016 at 2:19am
The new formula references a new simple query.  The query references an existing table, not a "dormant" one.  I don't think the formula is the issues, because if I delete a already used formula, performance goes back to normal.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Jun 2016 at 3:04am
I am not saying this is for sure what is happening in your report but it is frequently the issue when run times jump up. Crystal does not inherently enforce table joins. Just because the table was part of the report does not mean crystal is using it to create your data set. You have to either set the joins to enforced in the join or you have to use a field form the table in the report (in a formula counts as 'use'). Just because it is 'an existing table' does not mean it is part of your run time query. If your formula has a field from a table that had no other field used from it (including daisy chained tables) then the formula is 'enforcing' the join and changing your results.
The enforced join can make a result go from a few hundred rows to several million rows which also means doing calculations on all of those rows. If the table is not joined to other tables it can also cause a Cartesian result which can really make the row volume explode.
Like I recommended before, a quick and simple way to test this theory is to delete your formula and then (in preview mode) drag and drop each field used in the formula onto the report canvas (even just the report header) and see if one of these fields causes the report to 'slow down'.


Edited by DBlank - 01 Jun 2016 at 3:05am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.