Crystal Subreports are so slow 1

[datahound]

Hello,

I have created a Payroll Register that is run for a company with about 6000 employees and 40 pay elements per employee. Built a report in Crystal to represent each employee's pay information. The report has a report header containing employee/payroll information. And then there is a sub-report for earnings, deductions, employer costs, and taxes. Each of these is passing calculated parameters to fields outside of the sub-form. At the end of the report there are summary sub-reports (one for earnings, deductions, employer costs, and taxes) that represent the entire activity during the payroll run.

I use VB6 as the interface and the RDC for the reporting tool. The reports have to be continually used against different servers so I am always reconnecting and then passing recordsets to the main report and sub-reports to make them work.

The actual stored procedure that runs to collect the data takes just over a minute, and passing the recordsets to the report takes approximately another 25 seconds. Once I make the call to view the report, it takes 3.5 hours to return all 1600 pages. Is there some way to speed up that final step??? Attached is my code.

Thanks upfront.

Datahound

Dim Application As CRAXDRT.Application
Dim Report As CRAXDRT.Report

Private Sub CRViewer1_PrintButtonClicked(UseDefault As Boolean)

Dim intPaperOrientation As Integer
Dim intPaperSize As Integer

' Capture the default paper orientation for the report
intPaperOrientation = Report.PaperOrientation
' Capture the default paper size for the report
intPaperSize = Report.PaperSize

' Turn the use of the default printer off
UseDefault = False

' Allow the user to select a printer
Result = MsgBox("Would you like to use " & Printer.DeviceName & vbCrLf & "to print this report?", vbYesNo, "Printer Selection&quot

' If the select the default, output the report using defaults
If Result = vbYes Then
Report.PaperOrientation = intPaperOrientation
Report.PaperSize = intPaperSize
Report.PrintOut (True)
' Otherwise, bring up a printer selection window
Else
Report.PrinterSetup Me.hWnd
Report.PrintOut (True)
End If

End Sub

Private Sub Form_Load()

Dim rs As ADODB.Recordset

' Turn on the hourglass
Screen.MousePointer = vbHourglass

On Error GoTo Bailout

' Create a new Crystal Reports object
Set Application = CreateObject("CrystalRuntime.Application&quot
' Set the report equal to the reports path
Set Report = Application.OpenReport("H:\HRMS\Merger\GrahamsStuff\SmartStreamArchiving\CrystalReports\rptPayrollRegister.rpt&quot

' Disable drilldown
CRViewer1.EnableDrilldown = False
' Disable the tree view
CRViewer1.EnableGroupTree = False

' Reset the datasource location and log on information of the report
For Each dbtable In Report.Database.Tables
dbtable.SetLogOnInfo gstrServer, "PCPqrpt", gstrUserID, gstrPassword
Next

' Call Subreport Relink function
SubReportRelink

' Open the recordset containing the main data for the Payroll Register
Set rs = GetRecordset(GetConnection(), _
"EXEC PCPqrpt..zsp_rptPayrollRegisterFull " & "'" & gstrUserID & "'&quot

' Discard any saved data associated with the report
Report.DiscardSavedData
' Disable parameter prompting
Report.EnableParameterPrompting = False

' Set parameters
Report.ParameterFields(1).ClearCurrentValueAndRange
Report.ParameterFields(1).AddCurrentValue gstrUserID
Report.ParameterFields(2).ClearCurrentValueAndRange
Report.ParameterFields(2).AddCurrentValue "PCP"
Report.ParameterFields(3).ClearCurrentValueAndRange
Report.ParameterFields(3).AddCurrentValue "PCP"

Report.ParameterFields(4).ClearCurrentValueAndRange
Report.ParameterFields(4).AddCurrentValue "Payroll Register"

' If the recordset contains data, pass it to the report and open it
If rs.RecordCount > 0 Then
Report.Database.SetDataSource rs, 3
Report.ReadRecords
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
' Otherwise, throw an error
Else
MsgBox App.Title & " - There are no records to display from this criteria"
Screen.MousePointer = vbDefault
gbFrmSuccess = False
Unload Me
Exit Sub
End If

' Set the flag to state the form opened successfully
gbFrmSuccess = True
' Return the mouse to the default
Screen.MousePointer = vbDefault
' If the recordset has not been released, release it
If Not rs Is Nothing Then
' If the recordset is still open, close it before releasing
If rs.State = 1 Then
rs.Close
End If
Set rs = Nothing
End If
Exit Sub

BailoutContinue:

gbFrmSuccess = False
Screen.MousePointer = vbDefault
If Not rs Is Nothing Then
If rs.State = 1 Then
rs.Close
Set rs = Nothing
End If
End If
Exit Sub

Bailout:
MsgBox "The following error has occurred while you were " _
& vbCrLf & "attempting to open the payroll register: " _
& vbCrLf & Err.Number & vbCrLf _
& Err.Description

GoTo BailoutContinue
End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth
End Sub
Private Function SubReportRelink()

Dim rs As ADODB.Recordset

On Error GoTo Bailout

'Get the sections from the Main report
Set crxSections = Report.Sections

'Go through each section in the main report...
For Each crxSection In crxSections
'Get all the objects in this section...
Set crxReportObjects = crxSection.ReportObjects
'Go through each object in the reportobjects for this section...
For Each ReportObject In crxReportObjects
'Find the object which is the SubreportObject
If ReportObject.Kind = crSubreportObject Then
'Found a subreport, now get a hold of it
Set crxsubreportobj = ReportObject
'Open the subreport and treat it as any other report
Set crxSubreport = crxsubreportobj.OpenSubreport

Set crxtable = crxSubreport.Database.Tables.Item(1)

crxtable.SetLogOnInfo gstrServer, "PCPqrpt", gstrUserID, gstrPassword

If crxsubreportobj.SubreportName = "srptYo" Then
Set rs = GetRecordset(GetConnection(), _
"EXEC PCPqrpt..zsp_srptTaxesSummary " & "'" & gstrUserID & "'&quot
Else
Set rs = GetRecordset(GetConnection(), _
"EXEC PCPqrpt..zsp_" & crxsubreportobj.SubreportName & "'" & gstrUserID & "'&quot
End If

'Get the Tables collection for the subreport
Set crxTables = crxSubreport.Database.Tables
'Get the first table from the Tables collection
Set crxtable = crxTables.Item(1)
'Set the location of the table to the recordset
crxtable.SetDataSource rs, 3

End If
Next ReportObject
Next crxSection

Screen.MousePointer = vbDefault
If Not rs Is Nothing Then
If rs.State = 1 Then
rs.Close
Set rs = Nothing
End If
End If
Exit Function

BailoutContinue:

gbFrmSuccess = False
Screen.MousePointer = vbDefault
If Not rs Is Nothing Then
If rs.State = 1 Then
rs.Close
Set rs = Nothing
End If
End If
Exit Function

Bailout:
MsgBox "The following error has occurred while you were " _
& vbCrLf & "attempting to open the payroll register: " _
& vbCrLf & Err.Number & vbCrLf _
& Err.Description

GoTo BailoutContinue

End Function

Private Sub Form_Unload(Cancel As Integer)
' If, upon closing, there are only 2 forms in the Forms collection,
' show frmMain
Dim frm As Form
If VB.Forms.Count = 2 Then frmMain.Show

End Sub

 

[TedLiu]

hi,

Have you run it in report design mode? how long did it take?
Make sure report bring back only query data not all the data from database, especially you have more than one table join. Maybe you can check show sql query, to see where clause is correct or not, make sure won't have something like below:

select table1.column1, table2.column2
from table1, table2

if like this, report will bring back combination of table1 and table2, it will take extremely long time.

or maybe you can post your sql query in report designer here. let other guys figure it out for you.

cheers !

Ted

 

[datahound]

It's nothing to do with the SQL. The stored procs are returning the correct data and only what is necessary for each sub-report. It is the length of time that Crystal is taking to format the report.

 

[synapsevampire]

Subreports are slow...

Perhaps you can redesign the report to use fewer/none?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[TedLiu]

em..., how many subreports you have? the more subreports, the slower it will be, also could you try to not put subreports in detail section, it's also one of the reasons cause report slow.

I have developed big reports with 500k records. it was very slow, but won't take several hours.

another point is correct index in the query fields. but i assume you should have it already.

ted

 

[mlmcc]

Crystal suggests making all subreports ONDEMAND SUBREPORTS. That way until they are needed the subreport data isn't pulled. The main reason for that is, as you have found out, subreports are slow.

good luck
mlmcc

 

[pemarsh9999]

You won't believe the sense of deja-vu I had on reading this. We have exactly the same requirements (and pretty much the same result).

I'm not quite sure how you're attaching the sub-reports to the recordset. Is the same recordset used for every subreport, or do the sub-reports connect direct to the server to extract the data?

One quick tip that I can suggest is that we discovered that removing one Running Total field in one subreport (of six) gave an improvement in runtime of over 80%! This has got to be a bug in Crystal, but may explain your very long run times. Check for Running Totals and remove if possible (always possible to remove a running total, I think, and process in the stored procedure instead?).

HTH