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
  • 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

815 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now