Reduce the # of Database Reads 2

[ciscowiz]

I am using CR9 integrated into a VB.NET web app. All of our reports have a main view/stored procedure and a view called v_pubinfo which supplies personal company info such as Name, Address, and the 'logoletter' which is a character (d,f,g,t,etc.). We use the logoletter in the page header of the reports and set it to a custom font to display the company logo.

The problem is the report needs to make a read from the v_pubinfo view thousands of times per report per company which can become an overload. It is not the amount of data coming down it is the number of database reads for v_pubinfo.

Does anyone have any better solution to display logos or a way to reduce the number of DB reads?

Thanks,
Bryan

 

[BarbaraFuhrmann]

Hi,

I've never worked with CR 9 because we switched from 8.5 directly to 10. But I think that the things are not that different in between.

I would try to design a subreport and put it in the report header where it is executed only once. It reads the needed information from the v_pubinfo view and stores it in an shared variable (or multiple shared variables if you need more then just the logoletter). In the main report you can use the shared variable instead of what you are using now.

HTH
Barbara

Barbara Fuhrmann (Cologne, Germany) - Using Windows 2000/XP, Oracle 9i and Crystal Reports X

 

[ciscowiz]

Barbara,

Thanks for the help. I was thinking the same thing except do I need to use a subreport in the Report Header or can I just use the field/shared variable directly in the Report Header? We are trying to move away from subreports.

Thanks,
Bryan

 

[synapsevampire]

You can't have "a main view/stored procedure", it's one or the other.

Right, don't place the subreport in the page header, that means it will run on every page...

I don't understand "We use the logoletter in the page header of the reports and set it to a custom font to display the company logo.

The problem is the report needs to make a read from the v_pubinfo view thousands of times per report per company which can become an overload."

Why does it NEED to read it thousands of times, do you mean that it does, or that it needs to???

If you place the subreport in the report header and pass a shared variable, you can use it throughout the report.

Since it's in the report header it will run only once.

-k

 

[ciscowiz]

synapse,

I apologize for not being very clear with my issue.

The "/" in "main view/stored procedure" is meant to be read as "OR". The reason I said main is because every report has atleast TWO views (one is v_pubinfo) or a Stored Proc. and a view (v_pubinfo).

The two are NOT LINKED in the DB EXPERT. When I run a report and look at the SHOW DATABASE QUERY>> it does not include a reference to v_pubinfo. I don't know if this is where the porblem lies or not.

"Why does it NEED to read it thousands of times, do you mean that it does, or that it needs to???"

By NEED I mean the report does this on its own, not that I personally have a NEED for X number of reads, I want as few reads as possible and it seems like a waste of resources to make a read on the DB to bring back the "logoletter" field, which is a lone character, for every instance the logo is printed in the report.


I agree with the Report Header placement concept but what I don't understand is why I would need a subreport. Why couldn't I reference the field directly and place it in the variable?

Anyway, sorry again for not being clear and I appreciate the help. If you need me to clarify anything else, just let me know.

Thanks,
Bryan

 

[synapsevampire]

You can go ahead and try incluiding 2 different datasources in the main, I've seen it done, but Crystal doesn't support doing so.

I would rethink your processes and try to include all data in a single rowset. Otherwise, place the sub in the report header.

-k

 

[ciscowiz]

I didn't know that putting two datasources in the main was not supported. I don't think it would be feasible to go through and add the pubinfo code to all (over 200) views/SPs so I will try the subreport. Thanks Again!