Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Crystal Reports and MAS200 Report VERY SLOW!!! URGENT!

I am developing a report in Crystal Report 8 for use with a MAS200 DB. I have designed several reports now and sometimes they run abominably slow. I have a particular report with one subreport (can't use on demand, it passes a variable) that takes over 20 minutes to run.

It has 5 tables linked in the manager, and I am using some left-outer join queries in order to get the appropriate data. I am running things on the server, and I do have one parameter in the filter query.

I can't understand why this particular report would take so long.

Any advice would be appreciated.

  • 3
  • 3
1 Solution
If the 5 left-joined tables are in the subreport and the subreport is at the details or group level (so it's being generated multiple times) then that is potentially a lot of db work.  My first question would be how long does it take to run this query on the db itself?

VeeVanAuthor Commented:
I don't know. THe five joined tables are in the main report. The subreport has only one table in it.

How can I check how long it takes to run the query on the tables? I am using MAS200, and don't have access to a query builder in the tables.
VeeVanAuthor Commented:
here is a copy of my SQL statement, BTW:

    JC1_JobMaster."JobNumber", JC1_JobMaster."JobDescription", JC1_JobMaster."CustomerNumber", JC1_JobMaster."Estimator", JC1_JobMaster."Manager", JC1_JobMaster."ContractNumber", JC1_JobMaster."ContractDate", JC1_JobMaster."BillingMethod", JC1_JobMaster."JobStatus", JC1_JobMaster."StatusDate", JC1_JobMaster."EstimatedCompDate", JC1_JobMaster."ActualStartDate", JC1_JobMaster."DateReported", JC1_JobMaster."RevisedEstimate", JC1_JobMaster."RevisedContract", JC1_JobMaster."ActualPercentComplete",
    JC3_TransactionDetail."TransactionDate", JC3_TransactionDetail."SourceCode", JC3_TransactionDetail."TransactionComment", JC3_TransactionDetail."TransactionUnits", JC3_TransactionDetail."TransactionUnitCost", JC3_TransactionDetail."TransactionAmount", JC3_TransactionDetail."CostType", JC3_TransactionDetail."Billed",
    JCA_JobTypMasterfile."JobType", JCA_JobTypMasterfile."Description",
    JCC_CostCodeMaster."CodeCost", JCC_CostCodeMaster."Description"
    "JC3_TransactionDetail" JC3_TransactionDetail,
    "JCC_CostCodeMaster" JCC_CostCodeMaster,
    { oj "JC1_JobMaster" JC1_JobMaster LEFT OUTER JOIN "JCA_JobTypMasterfile" JCA_JobTypMasterfile ON
        JC1_JobMaster."JobType" = JCA_JobTypMasterfile."JobType"}
    JC1_JobMaster."JobNumber" = JC3_TransactionDetail."JobNumber" AND
    JC3_TransactionDetail."CostCode" = JCC_CostCodeMaster."CodeCost" AND
    JC3_TransactionDetail."SourceCode" <> 'PO'
    JC1_JobMaster."JobNumber" ASC,
    JCC_CostCodeMaster."CodeCost" ASC,
    JC3_TransactionDetail."CostType" ASC,
    JC3_TransactionDetail."TransactionDate" ASC
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Unfortunately I don't know anything about MAS200 so I can't be of much help there.  What I was getting at though is that Crystal can't execute a query any faster than the underlying database so if it takes 20 minutes to run on the db then it's going to take 20+ on Crystal.

My recommendation is to create the report using just 2 of the tables and see how long it takes to run that.  Then add a 3rd table and compare the time.  If you see a dramatic increase in time after adding a specific table then we know a little bit more about where to look.

Do you have any idea as to the size of these tables?
VeeVanAuthor Commented:
They are all pretty big. I have removed the subreport from the report and am looking to see how long it takes without the sub report. The interesting thing is when a VERY similar report runs from the database directly - it's a system report, not a Crystal report, it runs in a matter of seconds.
Does the similar report join the same tables based on the same fields?  If not then it could be an indexing problem on the database.

Also, the syntax in the SQL statement is unusual - did you write this manually or is this what Crystal generated?
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
frodoman's got your back.  Just to summarize troubleshooting points he's brought up and a few others:

1)  Where is the subreport located?  If its in the detail or group section, then this will affect performance.  If you bring back 5000 records and the subreport is in the detail section, this means the subreport will execute 5000 times.  Obviously, this is not ideal from a performance standpoint.

2)  How long does the base report query run on the server?  To the best of my knowledge, MAS200 runs on SQL Server.  I'd ask to have Query Analyzer installed on your workstation.  You should be able to just paste that query into the Analyzer and run it.  The base query doesn't include the subreport, so this will help you identify if that's a potential cause of the problem.

3)  Was this SQL generated by Crystal Reports?  Did you modify the SQL in Show SQL Query?  All of the joins should be represented in the FROM Clause.

4)  Please post your Record Selection Criteria. The only criteria that's not a JOIN is:

JC3_TransactionDetail."SourceCode" <> 'PO'

It's possible that other criteria is not being passed to the database and is, therefore, being filtered on the client.  This could cause tremendous slowdowns, depending on the number of records returned from the DB.

5)  There could definitely be an indexing problem on the DB.  I would troubleshoot all things Crystal first.  If we know that everything is good in Crystal (it doesn't look like it at this point, based on the SQL), then let's move on to troubleshooting the DB.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now