Trying to make Crystal Reports more efficient

[pl1101]

Is there a way to make Crystal reports run faster or more efficient? I am using Crystal XI against an AS400 database. I have 5 sql expressions and linking to 5 tables, but I am only pulling directly from 2 of them. There are 4 running totals and about 11 simple formulas. The 2 tables that I pulling from has about 50,000 in 1 and 100,000 records in the other.
FYI... I do not have access to do anything to the database itself.

"Don't know where to start"

Thank you for all of your assistance.

 

[GJParker]

The first place to start for any report is to make sure that all of the record selection formula is being paased to the database in the generated SQL.

Check under Report -> performance information -> processing -> record selection done on database should be yes.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Tatertot45]

go back through your report step by step and see if you can narrow the selection formulas, eleminate an uneeded table, or rewrite a formula.

Ex. Don't pull every order in the DB, then tag it as ordered, with the idea that you can use running totals to accumulate the invoiced ammounts. narrow your Selection formula to only pull invoiced orders. that will make your report more efficient becuase your later passes will have to deal with fewer records.

maybe posting more info on your report structure will help...

good luck...



Tatertot45
CRCP
Network+
A+

 

[pl1101]

I checked Check under
Report -> performance information -> processing -> record selection done on database should be yes.

and it says partial. Can I change this value?

 

[lbass]

You might find the following helpful: thread149-1276307 and
faq767-3825.

-LB

 

[GJParker]

You need to go through your record selection formula and work out which criteria are not being paased to the database you can check teh SQl from database menu -> show sql query.

If you ar using non database functions or variables in the selection criteria then these will be processed locally and will not be part of the database sql.

If you post your record selection formula and any associated formulas we shoudl be able to tell you what is causing the problem

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[pl1101]

SELECT DISTINCT "FGFC"."FCMO", "FGFC"."FCYR", "FKITMSTR"."IMDSC", "FGFC"."FCCNQ", "FGFC"."FCBK", "FKITMSTR"."IMSTS", "FKITMSTR"."IMSRTF", "FKITMSTR"."IMPN", "FGFC"."FCCSUM", "FKITMSTR"."IMAWRK", "FKITMSTR"."IMBUY", "FGFC"."FCCO", "FPWRKCTR"."WCSDES", "FPWRKCTR"."WCNO", "FTREQ"."DPQTY", "FKITMSTR"."IMQTOH", "FKITMSTR"."IMMPOC", (SELECT sum("OEPODTL"."ODIQTY")
FROM "BEAUTI1"."SAFILES"."OEPODTL" "OEPODTL"
WHERE "OEPODTL"."ODDTLS"<>'S' and
"FKITMSTR"."IMPN"="OEPODTL"."ODITEM"), (select sum(dpqty) from ftreq
where ftreq.dptno = fkitmstr.impn and
fkitmstr.imco = 1 and
(dpcsmo = 6) and
dpcsyr = 10), (select sum(fccsum) from fgfc
where fkitmstr.impn=fgfc.fcpn and
({FGFC.FCMO} = month({fn CURDATE()})and
{FGFC.FCYR} = 10)), (select sum(fccsum) from fgfc
where fkitmstr.impn=fgfc.fcpn and
({FGFC.FCMO} = month({fn CURDATE()})+1 and
{FGFC.FCYR} = year({fn CURDATE()}))), (select sum(dpqty) from ftreq
where ftreq.dptno = fkitmstr.impn and
fkitmstr.imco = 1 and
(dpcsmo = 7 and
dpcsyr = 10)), (select sum(fccnq) from fgfc
where fgfc.fcpn = fkitmstr.impn and
fkitmstr.imco=fgfc.fcco and
fkitmstr.imco = 1 and
(fcmo = 7 and
fcyr = 10))
FROM {oj (((("BEAUTI1"."KBM400MFG"."FKITMSTR" "FKITMSTR" LEFT OUTER JOIN "BEAUTI1"."KBM400MFG"."FGFC" "FGFC" ON ("FKITMSTR"."IMPN"="FGFC"."FCPN") AND ("FKITMSTR"."IMCO"="FGFC"."FCCO")) LEFT OUTER JOIN "BEAUTI1"."KBM400MFG"."FPROUTNG" "FPROUTNG" ON "FKITMSTR"."IMPN"="FPROUTNG"."RTPN") LEFT OUTER JOIN "BEAUTI1"."SAFILES"."OEPODTL" "OEPODTL" ON "FKITMSTR"."IMPN"="OEPODTL"."ODITEM") LEFT OUTER JOIN "BEAUTI1"."KBM400MFG"."FTREQ" "FTREQ" ON "FKITMSTR"."IMPN"="FTREQ"."DPTNO") INNER JOIN "BEAUTI1"."KBM400MFG"."FPWRKCTR" "FPWRKCTR" ON ("FPROUTNG"."RTCO"="FPWRKCTR"."WCCO") AND ("FPROUTNG"."RTWCNO"="FPWRKCTR"."WCNO")}
WHERE "FGFC"."FCCO"=1 AND ("FKITMSTR"."IMSTS"='A' OR "FKITMSTR"."IMSTS"='C') AND NOT ("FPWRKCTR"."WCSDES"='BACKFLUSH' OR "FPWRKCTR"."WCSDES"='MICRO' OR "FPWRKCTR"."WCSDES"='MICRO INSP') AND "FKITMSTR"."IMMPOC"='M' AND ("FKITMSTR"."IMPN"='12100' OR "FKITMSTR"."IMPN"='14308' OR "FKITMSTR"."IMPN"='15304' OR "FKITMSTR"."IMPN"='153069' OR "FKITMSTR"."IMPN"='15308' OR "FKITMSTR"."IMPN"='15309' OR "FKITMSTR"."IMPN"='16362' OR "FKITMSTR"."IMPN"='16426' OR "FKITMSTR"."IMPN"='16605' OR "FKITMSTR"."IMPN"='16779' OR "FKITMSTR"."IMPN"='18008' OR "FKITMSTR"."IMPN"='46830' OR "FKITMSTR"."IMPN"='46961')
ORDER BY "FKITMSTR"."IMBUY", "FKITMSTR"."IMPN"

I just copied the code from the "show sql"...

This query runs fairly fast, but when I take out the fkitmstr.impn criteria the report hangs up. This sql also shows the code for the sql expressions.

 

[dizzyjer]

If you have 5 tables linked to the report, and are only pulling information from 2 of them can you remove the other 3? I know that in the past this has helped with some of my reports.

 

[pl1101]

I read that I need to include the tables that I use to create the sql expressions. I took out 1 of the fields and removed 2 of the tables, and the report does run for a few minutes and then it freezes. Is there a way to find out on what record or at what stage the report hangs up?

 

[GJParker]

I would try an iterative approach. Try removing the sql expressions from the report and simply run the data from the 2 tables and see how lomg that takes, and then add back in the sql expressions one ata time and checking the performance at each stage.

You coudl aalso tyry running the sql expressions on their own and see what the performance of each is.

HTH

Gary Parker
MIS Data Analyst
Manchester, England