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

Posted on 2004-10-14
Last Modified: 2008-01-09
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.

Question by:VeeVan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 42

Expert Comment

ID: 12311584
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?


Author Comment

ID: 12311615
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.

Author Comment

ID: 12311626
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
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

LVL 42

Accepted Solution

frodoman earned 500 total points
ID: 12311671
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?

Author Comment

ID: 12311738
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.
LVL 42

Expert Comment

ID: 12311768
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?
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12312803
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.


Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question