Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-10-14
Medium Priority
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

LVL 42

Accepted Solution

frodoman earned 1500 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

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…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

618 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