Solved

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

Posted on 2004-10-14
7
430 Views
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.

Thanks.
Vee
0
Comment
Question by:VeeVan
[X]
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
7 Comments
 
LVL 42

Expert Comment

by:frodoman
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?

0
 
LVL 1

Author Comment

by:VeeVan
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.
0
 
LVL 1

Author Comment

by:VeeVan
ID: 12311626
here is a copy of my SQL statement, BTW:

SELECT
    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"
FROM
    "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"}
WHERE
    JC1_JobMaster."JobNumber" = JC3_TransactionDetail."JobNumber" AND
    JC3_TransactionDetail."CostCode" = JCC_CostCodeMaster."CodeCost" AND
    JC3_TransactionDetail."SourceCode" <> 'PO'
ORDER BY
    JC1_JobMaster."JobNumber" ASC,
    JCC_CostCodeMaster."CodeCost" ASC,
    JC3_TransactionDetail."CostType" ASC,
    JC3_TransactionDetail."TransactionDate" ASC
0
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

by:
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?
0
 
LVL 1

Author Comment

by:VeeVan
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.
0
 
LVL 42

Expert Comment

by:frodoman
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?
0
 
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.

~Kurt
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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