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`