Hard Drive Space Needed to Run Report

[Lisa5781]

Please help. I have a main report that contains seven subreports that are not connected. When I run the reports separately, everything is fine. When I run the main report, it bombs. I have two subreports that scan well over 50 million records on a SQL server included as subreports. My hard drive fills up with the temp records that Crystal creates before the data is displayed. I have not been able to find where I can change the location the temp files are written to.

Any ideas?

Lisa5781

 

[Ridyen]

I can't answer your question but I have to point out what you are asking this report to do.

You stated that the main report "contains seven subreports that are not connected". You also stated that you have "two subreports that scan well over 50 million records". You also stated that these reports work when run ok when run separately.

My head fills with the volumns you are talking about. If these subreports are in the DETAIL section of your main report, each of these subreports is being run once for every detail record. So if there were only 100 detail lines the number of rows processed in the report is at least 100+(100 x(2 X 50,000,000))or 10 billion records.

Big assumption on my part I know. Perhaps you could clarify a bit as to where your subreports are located. Obviously if place in the header or foot they would only run once. However still abit of data to process.

 

[Madawc]

Also check if you can exclude some records in Record Selection, or maybe group selection. When I first wrote Crystal, I'd collect most of the records and then suppress display of those I didn't want. That means doing all the work on the PC, whereas record selection is (mostly) done by the Server.

If you already do all possible selections in Record Selection, check Database>Show SQL Query and see if it is being done by the server. This should work whatever version of Crystal you are running (and it helps to say).

PS. I don't think you can change the place your temporary files are written to. But check if you've accumulated a lot of old and useless temporary files.

Madawc Williams (East Anglia, UK)

 

[Lisa5781]

The subreports (which are cross tab reports contained in the report header) were located in the details section (I've moved them to the report header).

I also had formulas to do my selection criteria but have now changed that to use record selection for each subreport.

My hard drive is not filling up now, but it is still taking about 30 minutes to run the report.

I'm new at this and have a lot to learn!

 

[Ridyen]

Sounds like you are heading a bit more in the right direction. If you can write stored procedures you should consider these for the record selection. Although Crystal Reports provide the ability to select records it is often more efficient to use a stored procedure select and provide the data. This mininmizes the number of records passed to Crystal to process.

 

[synapsevampire]

Ridyen: A Stored Procedure DOES NOT minimize the rows returned to Crystal.

A poorly designed Crystal Report will return more rows than required. An SP buys you a precompiled execution plan, and you can pivot data, etc, other than that, you have a few optimizer tricks that might help performance, but otherwise it's just SQL, which is what Crystal passes, inclusive of the record selection formula as SQL to the database.

Lisa: Check my FAQ:

faq767-3825

-k

 

[Ridyen]

Thanks snapese. Always willing to learn. However imho any query that processes against a table (datbase?) with 50 million rows should be optimized as much as possible. Especially if this is a task (report) this is going to be used on a regular basis.

Also in order to join tables from two different databases that reside on two different sql servers, does Crystal not have to bring all the records down in order to do the join? This type of join is common in our organization where each organization unit has their own database server.