SQL Server's Journal (original) (raw)
5:05p - improving performance in Crystal Reports with a local view..can it be done?
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 is a master plan of work to be done. It contains the MajorKey and some text fields that print in my report header
TableB is the individual tasks within that workplan. It contains the MajorKey, MinorKey and text fields that print in the report detail.
TableC is the person or people that will perform those tasks. It 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 is a list of all employees in the company. It contains NameKey and some text fields. This is what is in my subreport.
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.
A Workplan will have multiple tasks (sometimes 50 or more), and each task may have one or more people working on it.
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.
I've already had people tell me that subreports are a performance leech, and I should not use them. However, 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.
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.