Optimizing Drill Down Report

[mycrystalbaby]

Hi,
It would be great if somebody could help me with this problem.

I am using Crystal 10 with sql 8.0. I have a report with three drills down levels; second drill down has two on demand sub reports.

This report is extremely slow, as we have 50k records in the database. We have tried our best to optimize the sql view, but still the reports takes too long to run. When we run the view by itself, it is pretty okay.

On the first level, we have count of all the devices, which shows the count 50000 .
Is there a way, we can optimize this report within the application?
Thanks You!
AA

 

[Madawc]

Each occurence of a subreport causes a separate access of the data, which tends to be slow. Could you use some other method, such as crosstabs?

Another method is a Stored Procedure, collect your data using SQL.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

Sounds like your sub reports links are not specific enough or not being parsed to the database.

Are you linking on a formula within the subreport? Such a link will not be interpreted into the where clause on the report query.
If yes try and link on a field so that filter on subreport is more specific.

Alternatively build a specific view (or Stored Procedure as suggested by Madawc) that is used by the subreport to filter the data set more precisely.

Ian

 

[mycrystalbaby]

Thank you so much for responding back.
I was under thing impression that On demand sub reports dont access the data untill you dont click on the link.

Let me see, if I can have a stored procedure instead of using the table directly in the view.

Thanks again.
Anita

 

[IanWaterman]

You are right the On demand do not run until you select. However, I read you post as if it was the subreport which was slow.

If the main report is slow then its either your select statement not being very efficient, or you just have a lot of data to process. 50k records does not seem that big though.

When you say slow what does that mean, how long does it take to process report?

Ian

 

[mycrystalbaby]

The main report takes about 200 seconds to run. I was wrong; it has about 350,700 records to read.
What is does is, gives the count of those records in the first drill down.

View is very simple, it has one join between two tables..
So it is very straight forward data coming from sql.

Database people are saying, if we can do something in Crystal will be just great.
Thanks
AA

 

[IanWaterman]

Looks like you are stuck with this time. You have to bring back all 350 k records to the report and that is what takes the time.

You either need a faster connection to your database, or aggregate the data in a view/stored procedure on the database.

Ian

 

[mycrystalbaby]

Thanks again.
I did not understand what does it mean "Faster connection"?

Thanks.

AA

 

[IanWaterman]

Faster network connection, you are moving a lot of data.
Or a bigger/more powerful Oracle server

Ian