Crystal report with a lot of subreports takes a long time to load

Subreports put a lot of strain on the database and are inherently slow.  Can you change them to be ON DEMAND so they only open if the user selects one?

mlmcc

You can speed up the report by slimming down the number of subreports that you are using.

Try to see if and where you can use stored procedures..and / or SQL Expressions...IN PLACE OF data you are pulling in from SubReports...many times you can...

MikeV

Well, I watched MySQL administrator very closely and just what I thougtht happened.  It read from the database to fill the datasets that the report is based on (one for the subreport and one for the main report) and then after that the database is never accessed again but the report takes 3 minutes to generate.  

This report is a list of items as an invoice, which in turn, each has a list of options, and so the items are in the main report but the options for each are a sub report.

so
1. mlmcc I respect you, you have answered many a question I have looked up in the past but there is a small instantanious pull from the db  but the rest is just waiting for the system to generate the report.  The time required is definately directly related to the number of items and subreports)  but its NOT a db issue at all, so

2. based on the fact that db is not the issue your answer is invalid also.

3.   bdreed35 I almost want to yell at you.  Maybe Im missing something. but  I think since an item has a  indeterminatel collection of options its a situation where the number of sub reports is inadvertant but if you have ideas IM REALLY ALL EARS

I am elimating subreports in the cutsheet table by and report by determining all the data and putting it in a table.  I DO NOT want to do this in the invoice table if i can avoid it

I have never had someone tell me that they almost wanted to yell at me for offering assitance.  I don't know how to respond in a nice way so I am going to stop here...

Im just saying that I personally do not have any clue how to reduce the number of subreports for the options of items in an invoice I

I am sorry bdreed35 I did not come off right there, I just feel very frustrated by this situation and I dont see how when an invoice item has multiple options making its subreport and different invoices haev different items and options for those items how can I control the number of subreports.  I just felt liek the answer you gave was the "Captain Obvious" obvious answer I felt pretty stupid.

Mileya:

bdreed35 can speak for himself but I feel compelled to comment on this.

Mileya, I think you need to learn some simple manners in regard to forum etiquette. I don't appreciate your tone either. I think to insult experts on this forum is a gross disrespect to all experts here.

I wish to kindly withdraw any assistance on my part and wish you well on your endeavor to try and solve your own problem.

We are all professionals here and each of us possess varying levels of expertise...but ...experts we are.

Keep in mind that our time is very valuable and we do not wish to waste it frivolously.

Please keep this friendly advice in mind for the future.

Regards,
MikeV

First off, I appreciate the apology, and I accept it.

Second, keep in mind that when you post, the more information you can offer up front, usually translates into better repsonses from  experts.  We certainly aren't mind readers, so the more info is always better.

Basically, subreports require there own call to the db.  Depending on what section you put the subreport into, the subreport will be called as many times as that section runs.
Can you helps us understand the need for as many subreports as you have in the invoice?  I am not saying they are not necessary, just trying to understand what you are doing.

It may not be database related but each call to a subreport takes time even if the database isn't accessed.

Is the subreport in the details section?
If so why?

What SQL does the main report use?

What SQL does the subreport use?

Why do you need subreports?  
In many cases they can be eliminated through joining datasets.

mlmcc

Yes currently the subreport is in the detail section. Becuase it shows the options for each item on the invoice.  

main report query= select * from invoices, invitem, dealers, windowmodels where invoices.invbidid = invitem.invbidid and dealers.dealerid = invoices.dealerid and windowmodels.windowmodelid = invitem.winstyleid where InvID = Such and Such

Subreport Query = select * from invitemoption where ID = InvItemID

Im not sure what the best way to put this all together would be.

Glad i could help

mlmcc