Crystal Reports Running Very Slow

[Former Member]

Dear all,

I am not sure if it is by adding alot of tables into the report database or is it because of the formula or anything else but two of my reports are running really slow these days.

Both of the reports hold 7 tables altogether with 5 parameters of which 2 are dynamic. When I press F5 on my report sometimes it takes 1mins to load and some times good 5mins. Similarly while processing it takes from 6mins to good 15mins.

(Like now I ran the report for June month since 15:39 it is still running and now its 15:58 and it has finally loaded)

I simply do not understand why would the report take so long to load.. Once it load quicker and I checked the performance under Report performance and it stated 626ms Generate page, 2ms for formatting page.

On my recent attempt it states

Run the database query - 87238ms

Read database records - 30691ms

Format First Page - 981428ms

Average time to format page - 327143ms

Average time to generate a page - 58980ms

Time to format all charts (there is only 1) - 332014ms

I have similar reports with 6 tables and 4 parameters however they take flick of an eye and produces the results.

Is it to do with indexing on the database server? or my report coding has issues or something else?

I am using Crystal Reports 2008, ODBC connection to Informix Database 7.3.

Please advise on this

Regards

Jehanzeb

[former_member292966 · Accepted solution]

Hi Jehanzeb,

If you look at the SQL from the report, does it look ok?

Are the indexes being used?

Do you have a Selection Formula that isn't being translated to SQL?

The dynamic parameters, how many records is it loading?

Good luck,

Brian

[Former Member · Accepted solution]

Hello Brian,

If you look at the SQL from the report, does it look ok?

It looks fine to me, here is the SQL:

 SELECT DISTINCT order_header.order_no, order_header.order_status, order_header.date_entered,
ndmas.ndm_name, order_header.account_no, order_progress.order_status, order_lines.stock_code,
slslsp.slr_slsperson, slslsp.slr_slspname, order_progress.date_created, order_header.finish_type
 FROM   maxmast.ndmas ndmas, maxmast.order_header order_header, maxmast.order_lines order_lines,
maxmast.order_progress order_progress, maxmast.slcust slcust, maxmast.slslsp slslsp
 WHERE  (order_header.order_no=order_lines.order_no) AND
((order_header.order_no=order_progress.order_no) AND (order_header.repeat_no=order_progress.repeat_no))
AND (order_header.account_no=slcust.slm_custcode) AND (ndmas.ndm_ndcode=slcust.slm_custcode) AND
(slcust.slm_slsperson=slslsp.slr_slsperson) AND (order_header.date_entered>={ts '2008-06-01 00:00:00'}
AND order_header.date_entered<={ts '2008-06-30 00:00:00'})

Are the indexes being used?

You mean on Crystal? Well I have ticked the indexing on server option under report options

Do you have a Selection Formula that isn't being translated to SQL?

No all my selection formulas are set to see the database fields

The dynamic parameters, how many records is it loading?

I have no idea about this, all I can see is a long list of drop down menu in one of my dynamic parameter and in other there is only two (FF and UU).

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Oct 14, 2008 4:24 PM

On the second thoughts I cannot see Lens Type parameter under my SQL however the report is producing results accordingly. How can it be ?

My report selection formula is:

{order_header.date_entered} >= {?Start Date} and
{order_header.date_entered} <= {?End Date} and
(not HasValue({?Account Number}) or {order_header.account_no} = {?Account Number})and
(not HasValue({?Product Group}) or {order_lines.stock_code}[1 to 5] = {?Product Group})and
(not HasValue({?Sales Area}) or {slslsp.slr_slsperson} in {?Sales Area}) and
(not HasValue({?Lens Type}) or {order_header.finish_type} in {?Lens Type});

[former_member292966 · Accepted solution]

Hi,

I notice the SQL is only passing the date range and none of the other parameters. Crystal isn't able to translate HasValue into a valid SQL statement and that is one problem with the report.

Is order_header.date_entered an indexed field on the table?

I have a feeling the report is passing a very general query and downloading a lot of extra data down to your machine and applying the rest of the Selection Formula locally. Can you remove the HasValue part of your formula and see if that at least puts it into the SQL Query?

Thanks,

Brian

[Former Member · Accepted solution]

Hello,

Thats exactly what I just noticed. I don't think I can take off "Hasvalue" in actual report because that is the only way of getting the data sort according to parameter value or without it.

The main purpose of the report is to show all the orders according to number of days however having to have optional parameters. So if someone choose Account number then the report should filter accordingly and if someone leaves account number out then report should bring all the records according to date range.

That is the idea however it doesn't seem that it is working.

One thing I noticed that when I chose "F" from lens type from dynamic parameter it didn't get pass through SQL however the report produced right results.

So I guess it is sorting everything locally even though "Always Sort locally" option is unchecked.

What should I use (in selection formula) which will help me passing the parameter values if chosen to SQL?

Many thanks for your help

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Oct 14, 2008 4:46 PM

If I take hasvalue out then it does pass the query through SQL

 SELECT DISTINCT order_header.order_no, order_header.order_status, order_header.date_entered,
ndmas.ndm_name, order_header.account_no, order_progress.order_status, order_lines.stock_code,
slslsp.slr_slspname, order_progress.date_created
 FROM   maxmast.ndmas ndmas, maxmast.order_header order_header, maxmast.order_lines order_lines,
maxmast.order_progress order_progress, maxmast.slcust slcust, maxmast.slslsp slslsp
 WHERE  (order_header.order_no=order_lines.order_no) AND ((order_header.order_no=order_progress.order_no)
AND (order_header.repeat_no=order_progress.repeat_no)) AND
(order_header.account_no=slcust.slm_custcode) AND (ndmas.ndm_ndcode=slcust.slm_custcode) AND
(slcust.slm_slsperson=slslsp.slr_slsperson) AND (order_header.date_entered>={ts '2008-10-01 00:00:00'} AND
order_header.date_entered<={ts '2008-10-08 00:00:00'}) AND order_header.account_no='0113158' AND
order_lines.stock_code>='7-889'

[former_member292966 · Accepted solution]

Hi,

The Lens type doesn't go through because of the line:

{order_lines.stock_code}[1 to 5] = {?Product Group})

Crystal can't translate the parsing into SQL so it stops there. I'm not sure how it will affect the report but move this line to the bottom of the formula and it should translate the other lines at least.

Good luck,

Brian

[Former Member · Accepted solution]

I have just done that.

now the formula looks like this

{order_header.date_entered} >= {?Start Date} and
{order_header.date_entered} <= {?End Date} and
(not HasValue({?Account Number}) or {order_header.account_no} = {?Account Number}) and
(not HasValue({?Lens Type}) or {order_header.finish_type} in {?Lens Type}) and
(not HasValue({?Product Group}) or {order_lines.stock_code}[1 to 5] = {?Product Group}) and
(not HasValue({?Sales Area}) OR {slslsp.slr_slsperson} = {?Sales Area})

Though the time to produce a report is the same. I guess I need to check other things with database admin tomrow to find out what we can do to make it work faster (maybe creating tables in database according to direct fields which are needed).

Will report once done.

Many thanks for your help so far.

Regards

jehanzeb

[Former Member · Accepted solution]

This message was moderated.

[Former Member · Accepted solution]

To start, you should create your own thread for your question and not put it on someone elses thread.