Can a Crystal Report cause database/server issue?

[Former Member]

I was running a Crystal Report today which "crashed".  Our SQL server which holds the database was "almost out of memory"!

I stopped Crystal Reports on my local computer via task manager.

However, when the 3rd party database support team looked on the SQL server to diagnose the memory issue, they said the Crystal Report was still in an open loop, possibly creating the database to grow exponentially, use up memory, and then crash.

How plausible is this?  Has anyone else had similar issues with a Crystal Report?

I'm just not sure if the Crystal Report "exposed" an underlying issue or really is the culprit.

Just wondering if anyone has any input regarding this or tips in report design, etc. which could prevent this from happening.

Thanks.

[Former Member · Accepted solution]

Crystal report can be the culprit, if the report has complex sql, no filters, etc.,

To make db memory not to choke.

you can either tune the sql/ report, add extra filters if possible

(or) you may check sql server odbc/ jdbc properties to timeout after certain time.

(or) if you using business objects you can set connection timeout in CMC

[Former Member · Accepted solution]

Good information.

My report has a subreport and a crosstab as well.  Anything I should tweak with those?

i already have 4 entries in the selection expert for main report and sub report.  Does it matter what additional types of filters I add?  (Formulas, parameters - currently have none of those, etc.)

Thanks.

[DellSC · Accepted solution]

Are you using a Command (SQL Select Statement), Stored Procedure, or Universe in your report or are you joining tables together in the Database Expert?

- If you're joining tables together, go to the File Menu and select "Report Options".  Make sure that "Use Indexes or Server For Speed" is turned on.  Also, are you using any Crystal formulas in the Select Expert?  If so, please post the selection formula.

- If you're using a Stored Procedure, it MUST do to the data filtering.  Otherwise Crystal will bring all of the data into memory and filter it there, which would cause the DB problem you're seeing.

- If you're using a Command, ALL of the filtering must be done in the "Where" clause of the Command.  Otherwise it will behave the same way as a Stored Procedure, bringing all of the data into memory and filtering it there.

- If you're using a Universe, try to add any filters you require to the universe and use them from the universe when building the query.  Otherwise the same thing applies - everything will be done in memory.

-Dell

[former_member292966 · Accepted solution]

Hi,

Also take into account the subreports execute each time they are called.  So if the subreports are in the Detail section, they will query the database for each record your report generates.  So you aren't just querying the database once, you're querying the database for each subreport for each record.

I saw a report that crashed a database because of a subreport in the Detail section.  The main report was returning several hundred thousand records. The subreport was also retrieving several thousand records each time it executed.

So you aren't getting just the initial records from the main report, you're getting that number increased exponentially by the subreport.

Hope this makes sense.

Brian

[Former Member · Accepted solution]

Thanks for all the information.