Re: speed of report

[ecsrs]

I usually don't have any problem with the speed of my reports, but this particular one is giving me a problem. It is a basic grouping report that sums the data. There is a parameter for the dates. I run it weekly and don't have a problem as long as the week is within one month. But when I run it for the last week of the month and the months overlap (i.e. May 28th - June 3rd) the report takes about an hour. Any other time it takes a second to run. The other problem is that when I run this report it slows our database down to a stand still. Obviously making my end users very upset. Any suggestions or trouble shooting points?

 

[kmcginn]

I don't know why that would cause a speed problem, but could you run it for say May 28 through May 30 and dump it to a spreadsheet, then run it again for June 1 through June 3 and dump that to the spreadsheet? Just a thought.

I can't imagine why it would take longer if the dates cross two months - doesn't make sens to me.

 

[dgillz]

What is your database type?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[dk87]

One thing that can help is to modify the SQL statement. Make sure you make a backup first, because this can get tricky.

 

[ecsrs]

It is an SQL database. AN IT guy came down to my computer and thought it was the indexing within the database.

 

[danausten]

What version of Crystal are you using?

Are you using the option 'Perform grouping on server'? If not, try using this option.

The most probable cause for this is the date parameters are not being passed to the SQL query. If you are using CR9/10 change the report to use a SQL Expression and setup the date parameters for the expression.

HTH

 

[ecsrs]

I am using Crystal 8.5 and I have "Perform grouping on server" checked. Dose using 8.5 as opposed to 9 make a difference?

 

[danausten]

The difference would be that you won't be able to use Commands (SQL expressions).

 

[Madhi]

Hai ecsrs,
How are you passing parameter values? can you write the format of date you are passing?

Madhivanan

 

[ecsrs]

I have discovered what is causing the slowness to the report but I am still not sure if there is any solution. The table I am pulling from contains service units that have a client, date, # of units, provider and service type associated with it. Some services require you to put in daily units and other services require you to just put an aggregate monthly amount. In the second case the database automatically assigns the total # of units to the last day of the month. Therefore when I run a report that contains the last day of the month, the report takes forever to run because it has more records to query. However for my report I only need the services that require daily input not the ones that have the aggregate amount assigned to the last day of the month. Is there any thing I can do to speed up the query of this report? I hope I explained this clearly!!!!

 

[synapsevampire]

Rather than trying to use text for technical information, try posting:

Crystal version
Database/connectivity/version
Example dtaa
Expected output

That generally describes the environment more completely.

You might use the record selection criteria to eliminate the last day of the month if need be, but without understanding the table structures and how the data ios stored, it's hard to say.

-k