In running CR 8 on a Cache db that has about 2 million accounts and many transactions for each, I am running into such long run times that sometimes the run is not finished after several hours. Is there some setting I can use within Crystal that might help?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
setting to help problem of extremely long run times
- Thread starter stevesd
- Start date
Steve,
Couple things to look at:
if your data is on a server (other than the local machine) in File Options look at Perform Grouping on Server and also look at Use Indexes or Server for Speed.
I don't know what DB you are using, but if you know some SQL you can write a Query in Crystal that will substantially cut down on the processing time of your report.
Don
dmcleod@broadband.att.com
Couple things to look at:
if your data is on a server (other than the local machine) in File Options look at Perform Grouping on Server and also look at Use Indexes or Server for Speed.
I don't know what DB you are using, but if you know some SQL you can write a Query in Crystal that will substantially cut down on the processing time of your report.
Don
dmcleod@broadband.att.com
While this may be wrong by several orders of magnitude, assume that a query like the one you describe creates a dataset that is 20 gig in size. CR will have to bring this across the network, and build a temporary data file on the client in order to format the report. This is very time consuming.
Your most reliable solution is to reduce the size of the data set - unless you need every account and every transaction record to print on the report, which would mean the report is probably 100's of thousands of pages long.
Assuming that your report is under 10,000 pages long, most of the data is not needed (in detail form) on the report. So, write a view or stored proc that produces the output in the summary form that you need.
Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
Your most reliable solution is to reduce the size of the data set - unless you need every account and every transaction record to print on the report, which would mean the report is probably 100's of thousands of pages long.
Assuming that your report is under 10,000 pages long, most of the data is not needed (in detail form) on the report. So, write a view or stored proc that produces the output in the summary form that you need.
Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
- Thread starter
- #4
Malcom, thank you for your input. I would avoid a report of more than a hundred pages like the plague. On my last (CR 8) report, the output was only 39 records. The bulk of the processing time was spent reading the data base with CR 'not responding' according to the task manager on my desktop. CR apparently reads the WHOLE db before sending the few records it finally selects for my report. My response to your advice to reduce the size of the dataset is use of the parameters in CR to get only the few records I want. A few or a couple hundred is reasonable. Getting to that is the question.
This problem occurs more than people would like to think. One of the problems is if you use an If Then statement in the record selection, the SQL is not altered on anything you use in your record selection criteria. The sql will return all records and then parse them out on the 2nd pass based on the if then statement you are using.
Another problem may be that your table or view may not be indexed (at least properly for your purposes) causing a table scan to be done on your selection process.
The solution to either of these is simple. Use a subreport to either break out the items causing the if then or create a subreport for the non indexed table. Sounds wierd, but I have experience major time differences with this.
One other solution which may or may not be an item to look at. Horsepower may be an issue also. Find out what kind of machine that is powering you DB and have the DBA's do an analysis of the system when you are attempting to run the report.
Just some thoughts...
Another problem may be that your table or view may not be indexed (at least properly for your purposes) causing a table scan to be done on your selection process.
The solution to either of these is simple. Use a subreport to either break out the items causing the if then or create a subreport for the non indexed table. Sounds wierd, but I have experience major time differences with this.
One other solution which may or may not be an item to look at. Horsepower may be an issue also. Find out what kind of machine that is powering you DB and have the DBA's do an analysis of the system when you are attempting to run the report.
Just some thoughts...
Casman is right - this is a common problem. There are some solutions available, including ones that involve using conditional statements in the record selection formula.
A simple but very effective goal to keep in mind is that your SQL statement should closely reflect the dataset required for the report.
It is painful to bring all those records to CR, only to have 99.999% of them thrown away because CR could not translate your record selection formula in appropriate SQL.
So, what is your Record Selection formula?
Can you consider an upgrade to V8.5? Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
A simple but very effective goal to keep in mind is that your SQL statement should closely reflect the dataset required for the report.
It is painful to bring all those records to CR, only to have 99.999% of them thrown away because CR could not translate your record selection formula in appropriate SQL.
So, what is your Record Selection formula?
Can you consider an upgrade to V8.5? Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
- Thread starter
- #7
The selection formula selects about 6 fields and is limited by this, copied from the "show SQL query" screen.
WHERE
Acct.AC9 = Trans.AC9 AND
Trans.Cusip = '317xxxxxx' AND
Trans.TranType = 'TR' AND
Trans.MnyAmt > 0 AND
Acct.AC9 < 899xxxxxx AND
Trans.EffDate >= ? AND
Trans.EffDate <= ?
I put in x's to preserve confidentiality. I put in the limits for Trans.EffDate each time. When I limit that to a range of about a month, CR runs faster, meaning about two hours instead of a lot longer. Hope that helps instead of just muddying. Anyway, there are no if statements and still a very long run time.
WHERE
Acct.AC9 = Trans.AC9 AND
Trans.Cusip = '317xxxxxx' AND
Trans.TranType = 'TR' AND
Trans.MnyAmt > 0 AND
Acct.AC9 < 899xxxxxx AND
Trans.EffDate >= ? AND
Trans.EffDate <= ?
I put in x's to preserve confidentiality. I put in the limits for Trans.EffDate each time. When I limit that to a range of about a month, CR runs faster, meaning about two hours instead of a lot longer. Hope that helps instead of just muddying. Anyway, there are no if statements and still a very long run time.
The key lies in the difference between this and your Record Selection formula. What is your Record Selection formula (from Report|Edit Selection Formula|Record)?
Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
- Thread starter
- #9
Thanks, Malcom, I'm not sure I would have thought to look there. Here it is (edited for a little confidentiality again)
{Trans.Cusip} = "317xxxxxx" and
{Trans.TranType} = "TR" and
{Trans.MnyAmt} > 0.00 and
{Acct.AC9} < 8xxxxxxxx.00 and
{Trans.EffDate} in Date (1997, 06, 24) to Date (1997, 12, 17)
Those date fields {Trans.EffDate} get changed for each run.
{Trans.Cusip} = "317xxxxxx" and
{Trans.TranType} = "TR" and
{Trans.MnyAmt} > 0.00 and
{Acct.AC9} < 8xxxxxxxx.00 and
{Trans.EffDate} in Date (1997, 06, 24) to Date (1997, 12, 17)
Those date fields {Trans.EffDate} get changed for each run.
Hi stevesd,
Have you got any tables that a linked with an outer join.
This will cause a great deal of unnecessary searching.
Remove tables with the outer join, and put any value you still require from this table as a subreport which will only get done if the record is to be processed.
Geoff
Have you got any tables that a linked with an outer join.
This will cause a great deal of unnecessary searching.
Remove tables with the outer join, and put any value you still require from this table as a subreport which will only get done if the record is to be processed.
Geoff
- Thread starter
- #11
The SQL statement shows the join is an equal join, so I suspect that isn't the issue. All of the Record Selection formula is moving to the SQL statement, so that isn't the issue.
Don's original suggestion to try File|Options|Database tab|Perform Grouping on Server might do it - I find this is hit or miss - the limitations are outlined in the help file, and your detail sections must be suppressed. A view or stored proc that groups and summarizes the data is a more flexible option, but requires a bit more work to set up.
This is worth doing - if the result set is only a handful of records, and the processing is done on the server (by whatever method) the response time should be a few dozen seconds. Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
Don's original suggestion to try File|Options|Database tab|Perform Grouping on Server might do it - I find this is hit or miss - the limitations are outlined in the help file, and your detail sections must be suppressed. A view or stored proc that groups and summarizes the data is a more flexible option, but requires a bit more work to set up.
This is worth doing - if the result set is only a handful of records, and the processing is done on the server (by whatever method) the response time should be a few dozen seconds. Malcolm Wynden
Authorized Crystal Engineer
malcolm@wynden.net
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question