Improving performance while adding groups

[Former Member]

Hello,

I've been monitoring my crystal reports from a week or so and the report performance is going for a toss. I would like to narrate this in little detail. I have created 3 groups to select dynamic parameters and each group has a formula for itself. In my parameters I have added one parameter with 7 entities (which is hard coded), now a user can select any 3 entity out of those seven when initiallly refreshing the document, each of the parameter entity is bundeled in a conditional formula (mentioned under formula fields) for each entity. The user may select any entity and may get the respective data for that entity.

For all this i have created 3 groups and same formula is pasted under all the 3 groups. I have then made the formula group to be selected under Group expert. The report works fine and yields me correct data. However, during the grouping of the formula's crystal selects all the database tables from the database field as these tables are mentioned under the group formula. Agreed all fine.

But when I run the report the "Show SQL query" selects all the database tables under Select clause which should not be the case. Due to this even if i have selected an entity which has got only 48 to 50 records, crystal tends to select all the 16,56,053 records from the database fields which is hampering the crystal performance big time. When I run the same query in SQL it retrives the data in just 8 seconds but as crystal selecting all the records gives me data after 90 seconds which is frustrating for the user.

Please suggest me a workaround for this. Please help.

Thank you.

[former_member292966 · Accepted solution]

Hi,

I suspect the problem isn't necessarily just your grouping but with your Record Selection Formula as well. If you do not see a complete Where clause is because your Record Selection Formula is too complicated for Crystal to translate to SQL.

The same would be said for your grouping. There are two suggestions I can offer:

1) Instead of linking the tables in Crystal, use a SQL Command and generate your query in SQL directly. You can use parameters and at the very least, get a working WHERE clause.

2) Create a Stored Procedure or view that can use the logic you need to retrieve the records.

At the very least you want to be able to streamline the query to improve performance. Grouping may not be possible but my guess it's more with the Selection formula than the grouping.

Good luck,

Brian