Unsure how to fix this performance issue

[LMGroup]

I'm using Crystal Reports 2008, CR Developer 12.2.0.290. The data I'm access is in a SQL Server 2000 database. I'm using on ODBC connection.

I've created an absolute performance pig of a report due to the functionality demanded by the users throughout the company. It is a job docket that encompasses all the various aspects of our production. The report is set up to suppress any sections that are not required for each particular job. It has 33 subreports (I know, I know). Each of the subreports relate to one aspect of the production process and are supressed if no data is entered into that table (or that section of the table since some of them share tables). They are also laid out in the order that production has specified (although I may discuss that with them if there are performance gains to be realized by rearranging things).

I'm trying to attach it here for you to take a look at it, if you wouldn't mind. It runs fine on new computers, but the old clunkers we have in our production areas are bogging down trying to pull up the docket information.

Any suggestions for improving its performance would be greatly appreciated.

 

[LMGroup]

Correct file location...sorry.

 

[Madawc]

I assume you are accessing data directly in each of the subreports rather than running an SQL Command. Sometimes an SQL Command is the way to go.

More often, a slow report is doing most of its data selection on the machine that runs it rather than delegating the job to the server, which is built for just that. On Crystal 11.5, you could see this by choosing Database > Show SQL Query, and see what's not done.

The most common issue is date selection. Put a start-date / end date range in the Selection Formula > Record and it is slow. Set up the start date and end date as SQL Expressions, reference those in the Selection Formula > Record and suddenly it is vastly quicker.

This is just a guess. I have not tried downloading your source, no downloads allowed where I work.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[LMGroup]

I believe I am, yes. I'm not a SQL expert so I usually work within the constraints of the Crystal Interface. The selection is based one a single job number that the user is prompted for. The SQL statement for the main report is (this is ugly!):

SELECT "OpenJob"."JobN", "OpenJob"."ReprintCode", "CT_LMGeneral"."Location", "CT_LMGeneral"."Secret", "CT_LMGeneral"."PressCheck", "CT_LMGeneral"."FSC", "CT_LMGeneral"."Concerns", "CT_LMGeneral"."CARDescript", "OpenJob"."JobDescription", "OpenJob"."ExtraJobInfo", "OpenJob"."CustomerN", "Customer"."CustomerName", "Customer"."TelephoneN", "Salesperson"."Salesperson", "ProdPlanner"."PlannerName", "OpenJob"."DueDate", "CT_LMGeneral"."DueTime", "CT_LMGeneral"."PartialDate", "CT_LMGeneral"."PartialTime", "CT_LMGeneral"."PartialQty", "ProductCode"."PrDescription", "CT_LMGeneral"."LateDate", "OpenJob"."CustomersPON", "OpenJob"."CustomerOrderN", "OpenJob"."CustomerPartN", "OpenJob"."PreviousJobN", "CT_LMGeneral"."PrevCAR", "OpenJob"."BookedDate", "OpenJob"."OversAllowed", "CT_LMGeneral"."ContractRev", "OpenJob"."IssueNumber", "OpenJob"."Configuration", "CT_LMGeneral"."LinkDocket", "OpenJob"."ProductCode", "CT_LMGeneral"."Samples", "CT_LMGeneral"."ProductionNotes", "CT_LMGeneral"."BillWith", "CT_LMGeneral"."BillNotes", "CT_LMGeneral"."BillOn", "CT_SingleBindery"."TabNotes", "CT_LMGeneral"."FileInfo", "CT_LMGeneral"."TypeComm", "CT_LMGeneral"."TypeDigi", "CT_LMGeneral"."TypePV", "CT_LMGeneral"."TypeVary", "CT_LMGeneral"."TypeGaz", "CT_LMGeneral"."CustSample", "CT_LMGeneral"."CustMockUp", "CT_SingleBindery"."BindGlueNotes", "CT_SingleBindery"."BindGlueType", "CT_SingleBindery"."BindNumUp", "CT_SingleBindery"."BindSSNotes", "CT_SingleBindery"."BindSSOrient", "CT_SingleBindery"."BindSULC", "CT_SingleBindery"."BindSULCNotes", "CT_SingleBindery"."StampARPBar", "CT_SingleBindery"."StampARPBatch", "CT_SingleBindery"."StampARPCoil", "CT_SingleBindery"."StampARPNotes", "CT_SingleBindery"."StampBURSTDescr", "CT_SingleBindery"."StampBURSTNotes", "CT_SingleBindery"."StampBURSTReqd", "CT_SingleBindery"."StampOFDCAffixType", "CT_SingleBindery"."StampOFDCNotes", "CT_SingleBindery"."StampOFDCPlates", "CT_SingleBindery"."StampOFDCSize", "CT_SingleBindery"."StampOFDCTemplate", "CT_SingleBindery"."StampSIGBar", "CT_SingleBindery"."StampSIGBatch", "CT_SingleBindery"."StampSIGCoil", "CT_SingleBindery"."StampSIGNotes", "CT_SingleBindery"."StampVECTRACoil", "CT_SingleBindery"."StampWISTADieNum", "CT_SingleBindery"."PackBANDIn", "CT_SingleBindery"."PackBANDNotes", "CT_SingleBindery"."PackBANDWith", "CT_SingleBindery"."PackBOXNotes", "CT_SingleBindery"."PackBULKNotes", "CT_SingleBindery"."PackKWIn", "CT_SingleBindery"."PackKWNotes", "CT_SingleBindery"."PackKWWith", "CT_SingleBindery"."PackPKGNotes", "CT_SingleBindery"."PackPOLYin", "CT_SingleBindery"."PackPOLYNotes", "CT_SingleBindery"."PackPVInNotes", "CT_SingleBindery"."PackPVInQty", "CT_SingleBindery"."PackPVInType", "CT_SingleBindery"."PackPVOutNotes", "CT_SingleBindery"."PackPVOutQty", "CT_SingleBindery"."PackPVOutType", "CT_SingleBindery"."PackSWin", "CT_SingleBindery"."PackSWNotes", "CT_SingleBindery"."PackSWwith", "CT_SingleBindery"."PackBOXQty", "CT_SingleBindery"."PackBOXSize", "CT_SingleBindery"."PackPALLETtop", "CT_SingleBindery"."PackBOXType", "CT_SingleBindery"."PackPALLETType", "CT_SingleBindery"."PackSTRAP", "CT_LMGeneral"."EnvLogo", "CT_LMGeneral"."QuoteNum", "CT_LMGeneral"."LibrarySamples", "CT_SingleBindery"."PackBULKIn", "CT_SingleBindery"."PackPALLETNotes", "OpenJob"."Quantity", "CT_LMGeneral"."CustomerSamples", "CT_LMGeneral"."CSRSamples", "CT_LMGeneral"."Contact", "CT_LMGeneral"."CARN"
 FROM   ((((("LoweMartin"."dbo"."OpenJob" "OpenJob" INNER JOIN "LoweMartin"."dbo"."Customer" "Customer" ON "OpenJob"."CustomerN"="Customer"."CustomerN") INNER JOIN "LoweMartin"."dbo"."ProdPlanner" "ProdPlanner" ON "OpenJob"."ProdPlanner"="ProdPlanner"."ProdPlanner") INNER JOIN "LoweMartin"."dbo"."Salesperson" "Salesperson" ON "OpenJob"."SalesmanN"="Salesperson"."SalespersonN") INNER JOIN "LoweMartin"."dbo"."ProductCode" "ProductCode" ON "OpenJob"."ProductCode"="ProductCode"."ProductCode") LEFT OUTER JOIN "LoweMartin"."dbo"."CT_LMGeneral" "CT_LMGeneral" ON "OpenJob"."JobN"="CT_LMGeneral"."JobN") LEFT OUTER JOIN "LoweMartin"."dbo"."CT_SingleBindery" "CT_SingleBindery" ON "OpenJob"."JobN"="CT_SingleBindery"."JobN"
 WHERE  "OpenJob"."JobN"=597070

This is everything that is pulled outside of the subreports.

 

[Madawc]

So match that to your LINC selection.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[LMGroup]

I'm sorry. I'm not sure what you mean. That code is generated by what is set up in the main report. The subreports are linked using the JobN field. That's the common denominator in all the tables.

Sorry for being so thick This is the most complex report I've created.

 

[Madawc]

Do you have date selections? If not, then it's something else and I can't help.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[LMGroup]

No. No date selections. Just the job number. Thanks for trying

 

[lbass]

You are posting in the incorrect forum. In the future, you should post in forum149 or forum767, depending upon your question.

Are you using a parameter for JobN? If so, you might want to try creating the same parameter in the subreports, adding the parameter to the record selection formula of each, and linking the subs to the main report by linking the parameters together instead of the fields. Might help. When linking the parameters, use the dropdown in the lower left to select {?Parameter} instead of the default {?pm-?Parameter}.

You might find the following helpful also: thread149-1276307.

-LB