Improving performance with a local view - is it possible?

[BillYater]

I am experiencing some major performance problems on a report I have written in Crystal 10. It will require a little explanation.

I have a CR10 report that has a header section based on a MajorKey, a detail section based on a MinorKey within the MajorKey, and a subreport based on a SubKey within the MinorKey.

The MajorKey is passed into my report, and I want to see the details and the subreport for this key.

My tables are as follows:
TableA contains the MajorKey and some text fields that print in my report header
TableB contains the MajorKey, MinorKey and text fields that print in the report detail.
TableC contains the MinorKey, SubKey, a NameKey and text fields. This is a linking table between the detail and subreport, and no fields from this table are printed.
TableD contains NameKey and some text fields. This is what is in my subreport.

I am trying to improve performance on the subreport. Rather than having all of those tables in the subreport, I have created a view in SQL server that is as stripped down as possible. The view is essentailly:

select tableB.MinorKey, TableD.textfield
from TableC
INNER JOIN TableD
on D.NameKey = C.Namekey
INNER JOIN TableB
on B.MinorKey = C.MinorKey

In the database I am running against, this gives me a view of around 7000 records. This number will obviously increase as users enter more data in the system.

My subreport contains 1 field - View.Textfield. The Subreport is linked to the Detail report with the RecordSelection condition: View.MinorKey = Parameter. Parameter gets set to B.MinorKey in the detail report.

When I run this report, I am getting terrible performance. When I select a value for MajorKey that should return 15 detail lines, and between 1 and 3 rows in each subreport, it takes between 1:15 and 1:30 for the report to complete. If I select a MajorKey that returns 47 detail rows, it takes between 4:00 and 4:45. If I try a MajorKey that returns 95 detail rows or more, it takes over 10 minutes, if it returns at all (I've let it run for 30 minutes once without success).

I think I know the reason; the subreport gets called for each of the detail rows, and each time, it has to slog through a 7000 record view to find the one or two records that match. I've tried indexing the view, but that didn't help.

What I want to know is if it is possible to have a parameterized view (or read-only cursor, or whatever the appropriate term would be) within the report. When I run the report and pass in a MajorKey of 12345, have a SQL statement run that executes my view statement, but with the added WHERE clause of WHERE A.MajorKey = 12345. In that case, I would only return a set of records that is specific to that MajorKey, and would have a few dozen or hundred records instead of the full set. Then in my subreport, use that local view. A lot fewer records to slog through. I know that tables and views that are part of the main report environment are not necessarily visible to the subreport. Also since this view would be paramterized, I don't know if I can (or should) store it in my SQL database or if it would only exist in the context of the report, being created when the report starts and destroyed when it exits.

Although I have a lot of reporting experience in other tools, I am pretty much a novice in Crystal Reports. I don't know if what I am proposing is technically possible, and if it is, I would appreciate some assistance on creating the view in my report file.

 

[synapsevampire]

You should be designing to avoid the subreport, nit create a View within it.

Keep in mind that you can paste the entire SQL in for your report in CR 10 using the Add Command, and that haviing row inflation in the report so that you return all data in one set will outperform your approach, you'll just need to suppress duplicates.

-k

 

[fredp1]

Hi Bill,

I'm finding it difficult to see exactly what you need.
Can you provide a sample output and also the tables with the approriate data for the output.

I can't tell if you just need a 'complex' query that can be done in Crystal, or whether you need a stored procedure with a parameter, although with v10, this might not be necessary.

Can you also explain why you used a sub report, as apposed to a 'complex' query with the joins in the main report?
(maybe its just inexperience?)

If you can post the above details, it will help to clarify on what you need better.


Cheers
Fred

 

[BillYater]

I wish I could provide more detail, but this is sensitive data (client is a bank) and I can't post it here.

I can elaborate on my description somewhat:
TableA is a master plan of work to be done.
TableB is the individual tasks within that workplan.
TableC is the person or people that will perform those tasks.
TableD is a list of all employees in the company.

The report in question lists all of the tasks for a given workplan, and the subreport lists all of the people that are working on a given task.

Workplan 12345 may have 50 tasks, and each task may have one or more people working on it.

Another developer mentioned using a stored procedure. I've usually used stored procedures for database updates, or processing of data that will return a value, but I don't know if it would give me faster performance in a simple SELECT statement vs having the data in a view. I'd still have to bang on the database for each task. I was looking for something that gets me all of the data for a workplan, then just work with that subset of data in memory.

Also, I don't have the latitude I would like with the report. This is not a new report, but an existing one that was migrated from CR8.5. I have been told to modify it for performance, but not to make any major changes that would require extensive testing. I'm a patch-em-up guy, and don't have the authorization to rewrite the report in it's entirety. Nor do I have the expertise; I've been stumbling my way through Crystal for only about a week.

 

[synapsevampire]

You don't need to post actual data, make up numbers/text...

I never understand the insistence of people to write text descriptions instead of providing meaningful examples.

Those that say improve performance but don't make major changes are contradciting themselves.

It was poorly designed, and the performance is based on that fact.

Using a Stored Procedure is a VERY major change, and I would listen to them, use a SP or a View, whatever, just avoid subreports.

-k

 

[fredp1]

As synapsevampire mentioned you can make up the data...

Any way, from the limited amount of info that i'm seeing, why can't you just have the main report with all the tables linked together
A=B=C=D. It looks like they are all inner joins.
If you have any filters on the table B, C and D then I can see the need for the subreport... but there are work arounds.
As they say, the devil is in the detail. Hopefully more will be forth comming.




Cheers
Fred

 

[BillYater]

Unfortunately, this subreport is actually one of three subreports in the detail of the main report. The task table (tableB) can also have sub-assemblies (sort of like a task within a task), and we also list any materials needed for this task. Rewriting this will be a major undertaking. I was looking for some way to avoid it.

I will work on cobbling together some sample data.