Report runs slow when more then 11 formulas in the report

[dzavad]

I have simple report with simple linkage and not much of the data in the db. I noticed that my report running extremely slow. I start to nail the problem down by cutting off amount of formulas in the report. I have 13 formulas and logic in it identical except for the field name. Logic is:
if {tab1.field} = [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"] then 1 else 0
So basically I collect 1 and 0 and total them in 14th formula like:
({@Check 1} +{@Check 2}+{@Check 3}+{@Check 4}+{@Check 5}+{@Check 6}
+{@Check 7}+{@Check 8}+{@Check 9}+{@Check 10}+{@Check 11}+{@Check 12}+{@Check 13}) /13 * 100
if I take any two formulas out of equation then report runs fast. Adding any of them back and report slows down dramatically.
I can’t find anything wrong with the report.
Any suggestions?

 

[synapsevampire]

Basic information such the version of Crystal and the database used are critical to improving performance, much more so that text descriptions.

Generally speed is based on the SQL being passed.

Too bad you didn't share one more formula so we had an idea of your approach.

Anyway, you might try using Running Totals instead:

Place a distinct field, select count, in the evaluate use a formula place:

{tab1.field} in [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"]

You can then reference the RT's in a formula later.

The fastest way would be to do this on the database, whcih might be accomplished by building a database object (View or SP), or from within some versions of Crystal, use a SQL Expression.

-k

 

[dzavad]

Sorry I forgot to mention that I use Cr 9 and MySql db

 

[dzavad]

Another formulas looks like :
if {tab1.field 2} = [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"] then 1 else 0

if {tab1.field 3} = [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"] then 1 else 0

if {tab1.field 4} = [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"] then 1 else 0

 

[dzavad]

Also report run fine in the tes db but start give problem in production...So I asume that hapening because of a lot more records in production db.

 

[synapsevampire]

Ahhh, if you're trying to limit rows returned by the database to only those values, use Report->Selection Formulas->Record and place something like:

(
{tab1.field 2} in [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"]
)
or
(
{tab1.field 3} in [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"]
)
or
{tab1.field 4} in [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"]
)

In theory, this will then be passed to the database for processing. In essence anything you place in the Report selection formulas should be reflected in the Database->Show SQL Query. The Show SQL Query demonstrates what is being passed to the database.

-k

 

[dzavad]

is there limits on how many tables can be joined in one report? reports running faster with 13 tables rather then 15 joined tables.

 

[synapsevampire]

The return time is probably based on the database.

The limitations are generally connectivity related.

As you may know, MySQL isn't exactly ready for primetime in all respects.

But you're discussing 13 FIELDS in the report, NOT 13 tables.

Are you bouncing around in your questions between 13 fields and 13 tables?

And I see no reaction to the suggestions here, just more questions, are you testing anything submitted?

-k

 

[dzavad]

First of all..Thanks for the help...I do test the previus suggestions. It works much faster. But I am having General problem hot just one report. I was just trying to solve problem with one to see if that change can be aplied to other reports, because all of the afected reports use the same tables. What happend is that there is actualy 14 tables in the report. and one main table innerjoins each other one (13 of them). We have only 100 records in the db. but it returns resaults in 10 min. I was trying to isolate the problem by removing fileds from the report and findout that if I remove 2 out of the 13 formulas from the report it runs for .2sec. Each formula is belongs to diferent table: Example
formula1
if {tab1.field 2} = [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"] then 1 else 0
formula2
if {tab2.field 3} = [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"] then 1 else 0
formula3
if {tab3.field 4} = [ "Compliance","Non-Compliance","Not Applicable","Part. Compliance"] then 1 else 0
and so on.......
Sorry I didnt specify all the details..I thoght it wasnt nesesary in the begining...
Here is SQL statements CR9 generates:
SELECT `Case_Info`.`FS_File_Number`, `Case_Info`.`Caseload_No`, `CM_1`.`Screening_and_Approach_text`, `CM_10`.`Developing_a_Plan_text`, `Completion`.`Audit_Complete_Date`, `CM_4`.`Family_Development_Response_text`, `CM_5`.`Determine_Time_Frame_text`, `CM_7`.`Seeing_and_Interviewing_text`, `CM_8`.`Concluding_Investigation_text`, `Case_Info`.`Date_Audit_Start`, `Case_Info`.`Office_Code_text`, `CM_2`.`Child_at_Risk_text`, `CM_3`.`Assessing_a_Report_text`, `CM_6`.`Conducting_Investigation_text`, `CM_11`.`Reassessing_a_Plan_text`, `CM_12`.`Notification_of_Fatalities_CFS_text`, `CM_13`.`Notification_of_Fatalities_Reportable_text`, `Completion`.`Audit_Complete_text`, `CM_9`.`Concluding_Investigation_Timely_text`, `Case_Info`.`Official_Audit_text`
FROM (((((((((((((`bc-cfs`.`Case_Info` `Case_Info` INNER JOIN `bc-cfs`.`CM_1` `CM_1` ON `Case_Info`.`RecId`=`CM_1`.`RecId`) INNER JOIN `bc-cfs`.`CM_10` `CM_10` ON `Case_Info`.`RecId`=`CM_10`.`RecId`) INNER JOIN `bc-cfs`.`CM_11` `CM_11` ON `Case_Info`.`RecId`=`CM_11`.`RecId`) INNER JOIN `bc-cfs`.`CM_2` `CM_2` ON `Case_Info`.`RecId`=`CM_2`.`RecId`) INNER JOIN `bc-cfs`.`CM_3` `CM_3` ON `Case_Info`.`RecId`=`CM_3`.`RecId`) INNER JOIN `bc-cfs`.`CM_4` `CM_4` ON `Case_Info`.`RecId`=`CM_4`.`RecId`) INNER JOIN `bc-cfs`.`CM_5` `CM_5` ON `Case_Info`.`RecId`=`CM_5`.`RecId`) INNER JOIN `bc-cfs`.`CM_6` `CM_6` ON `Case_Info`.`RecId`=`CM_6`.`RecId`) INNER JOIN `bc-cfs`.`Completion` `Completion` ON `Case_Info`.`RecId`=`Completion`.`RecId`) INNER JOIN `bc-cfs`.`CM_7` `CM_7` ON `Case_Info`.`RecId`=`CM_7`.`RecId`) INNER JOIN `bc-cfs`.`CM_8` `CM_8` ON `Case_Info`.`RecId`=`CM_8`.`RecId`) INNER JOIN `bc-cfs`.`CM_9` `CM_9` ON `Case_Info`.`RecId`=`CM_9`.`RecId`) INNER JOIN `bc-cfs`.`CM_13` `CM_13` ON `Case_Info`.`RecId`=`CM_13`.`RecId`) INNER JOIN `bc-cfs`.`CM_12` `CM_12` ON `Case_Info`.`RecId`=`CM_12`.`RecId`
WHERE (`Case_Info`.`Date_Audit_Start`>={d '2004-01-01'} AND `Case_Info`.`Date_Audit_Start`<={d '2004-12-31'}) AND `Completion`.`Audit_Complete_text`='Yes' AND `Case_Info`.`Official_Audit_text`='Yes'
ORDER BY `Case_Info`.`Office_Code_text`, `Case_Info`.`FS_File_Number`

 

[dzavad]

I am trying to make sure that I am not having problems on Crystal side and it is My Sql isue....At this point I am trying rework the logic in CR report. Running totals did the job well. BUt now I have to make changes in a lot of the reports...Am Ion the right track? I am asking my self that.

 

[synapsevampire]

Now the3 example shows different tables for each field, the original post did not...

The whole approach seems whacky now, and a nice waste of time for all.

I don't need to see what's being done, I need to know what you have, and what you need.

It seems that a Union in a View would handle this nicely on the database side, if MySQL supports that yet.

Try to minimize the code and complexity in Crystal, let the database do all of the work.

Post:

Example data (tables.fields and data types)
Expected output

You're pigeonholing everyone into trying to work within your devised architecture.

Anyway, things look relatively well optimized from a where clause perspective.

-k