StrataFrame Forum » .NET Forums » General .NET Discussion » Report Sharp-Shooter slow preview process

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Fri, 11 Dec 2009 15:16:17 GMT

Hi Larry,

Could it be possible for you to create a small sample application using the SF sample database to show this problem? This way it would be easier for Trent to figure out a fix or path to a solution.

Report Sharp-Shooter slow preview process

Edhy Rijo — Fri, 11 Dec 2009 15:16:17 GMT

Hi, I know this is a RSS issue, but since many SF developers use RSS I will throw it here anyway :hehe:

I have this report which is taking about 15-24 seconds to show in my custom preview form, the preview form will show up blank with the "No Report" label (see image RSS slow loading), then the report will be shown normally.

I know that the data is coming out in less than a second, and the preview form is shown without the report, I have not found a way to display a message to the user letting him/her know that the report is being process. The code to generate the report is splitted in two classes:

ServiceCallRouteDataSource.vb will take care of getting the data and pass it to the report with the following code:

[codesnippet][code]

'-- Assign the populated datasource from the Dialog Browser to the Report Manager Object.

ReportManagerObject.DataSources.Item(reportDataSourceName) = Me.DataSource

ReportManagerObject.DataSources.Item(reportApplianceDataSourceName) = Me.DataSource.SourceBO.SC_Appliances

'-- Prepare Report to run

ReportManagerObject.Reports(Me.PerpetuumSoftReportName).Prepare()

[/code][/codesnippet]

ReportEngine.vb will actually run the report in the custom preview form like this:

[codesnippet][code]

Public Shared Function RunReport(ByVal ReportName As FixTrackReports, _

ByVal ReportManagerObject As PerpetuumSoft.Reporting.Components.ReportManager, _

Optional ByVal tBDStringValue As String = "", _

Optional ByVal tEmployeePK As Int64 = 0) As Boolean

Dim reportDataSourceObject As IReportDataSource

'-- Get the data source for the report

reportDataSourceObject = GetReportDataSource(ReportName)

'-- Populate the data source

If reportDataSourceObject.PopulateDataSource(ReportManagerObject, tBDStringValue, tEmployeePK) Then

Dim f As New ReportViewerDialog()

'-- Set the Report Viewer source with the Report to be used.

f.rptViewer.Source = ReportManagerObject.Reports(reportDataSourceObject.PerpetuumSoftReportName)

f.Title = MicroFour.StrataFrame.Tools.Common.GetEnumDisplayValue(ReportName)

f.TitleDetailText = ""

f.ShowDialog()

Return True

Else

MicroFour.StrataFrame.Messaging.MessageForm.ShowMessageByKey("NoDataForReport")

Return False

End If

End Function

[/code][/codesnippet]

I have other simple reports which generates faster, so I must be missing something in my whole process. I will appreciate if somebody can see what am I missing here?

Thanks.

RE: Report Sharp-Shooter slow preview process

Larry Caylor — Fri, 11 Dec 2009 13:27:19 GMT

Hi Edhy,

I'm using SF v1.7.0.2. I'm no expert with ANTS memory profiler but I ran two profiles against the same report, one using a BBS and another using a dataset. I took memory snapshots after the data query (my baseline) and after the report finished rendering. What I found was that using the BBS in addition to being significantly slower uses a lot more memory. By the time the report was rendered, I had one instance of the BO for each record retrieved. This resulted in a memory increase of about 42 MB for the BO class. There was also a 45MB increase in the Stack<Delegate> class. For the BBS the number of bytes in all heaps increased from 50 to 213 MB and Private bytes went from 100MB to 270MB.

In contrast when using a dataset, the number of bytes in all heaps went from 78 to 108 and private bytes went from 123 to 163. It would appear that using a BBS is expensive in terms of memory but I'm not sure if this is the only thing impacting the total rendering time.

Am I the only one having performance issues rendering a large number of records? The BO I'm using for the BBS is very straight forward and doesn't contain any custom fields or scalar methods. I'm not sure if this is to be expected when using a BBS as a reporting source or if this is a problem that could be fixed. This would be a good point for Trent to jump in.

-Larry

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Thu, 10 Dec 2009 19:07:00 GMT

Hi Larry,

Very interesting, it looks like the BBS may have some sort of memory leak to cause this overhead. Now that you mentioned this, I had a similar situation in the past not with RSS but trying to load several hundred thousands of record in a BO.

Are you using the latest SF version 1.7.0.x?

I wonder what Trent have to say about your testing?

RE: Report Sharp-Shooter slow preview process

Larry Caylor — Thu, 10 Dec 2009 18:54:54 GMT

Hi Edhy,

Since I found few performance issues on the RSS forum I decided to do a little testing. The BBS/BO that I'm using is mapped to a view and I select records by date range. The acutal loading of the BO is not an issue, just the rendering time. I ran another set of tests, one set with the SF BBS/BO and the same report and select criteria using an ADO Dataset. The query times were similar but the report rendering times showed a drastic improvement. It would appear that the SF BBS/BO adds additional overhead or there is some issue with RSS using it as a data source. For example 69K records took about 635 seconds to render using a BBS as the datasource but only 23 seconds using a Dataset. The only difference between the two sets of tests in the datasource.

-Larry

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Wed, 09 Dec 2009 13:19:56 GMT

Hi Larry,

Like you I believe there is a problem with rendering. I found this post in RSS forum that may help you [url=h]ttp://http://www.perpetuumsoft.com/ForumTopic.aspx?topic_id=1869&forum_id=9[/url] if not try posting a message in the RSS forum and lets see what they have to say.

RE: Report Sharp-Shooter slow preview process

Larry Caylor — Wed, 09 Dec 2009 10:31:45 GMT

Hi Edhy,

I've got it set to single pass and I do have both a detail and summary report. However for the summary I do all of the grouping and counts in a SQL stored procedure and return just the summary records, which is very fast. I originally did the summarization in RSS but it was very slow. It would appear that even if you can query the data quickly it just takes RSS a substantial amount of time to process a large number of records even if they do not end up being printed.

-Larry

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Tue, 08 Dec 2009 20:01:53 GMT

Hi Larry,

Make sure that the report property "DoublePass" in the master document is set to false, or it will take 2 passess to generate the report.

Also will you really be printing 70,000 records? I have not tried RSS with that many records, but in your case maybe you can approach the need to show the report with a summary of records.

RE: Report Sharp-Shooter slow preview process

Larry Caylor — Tue, 08 Dec 2009 18:27:15 GMT

Forgot the attachment.

RE: Report Sharp-Shooter slow preview process

Larry Caylor — Tue, 08 Dec 2009 18:25:54 GMT

I'm either having a performance issue or overly optimistic expectations for RSS. I have what I consider to be a simple report (single data source, two group bands, a detail line, and about 6 fields). The user selects data by date range which usually results in 20K - 100K records. There is a detail line for each record. The query to fill the BBS is relativly fast but the report rendering is slow or I have unrealistic expectations:unsure: There are no scalar methods or custom fields in the BO. I've implmented IRenderSite so I can monitor the report progress and I can see that rendering is what is taking the time. A report with 70K records takes about 10 minutes. I've run the same report with varying number of input records and have included a graph of the results. Is this normal or is there something that I should be looking at to improve the rendering times? I'm using RSS v 4.0.3.5, the latest SF release and Windows XP. The rendering times were taken on a compiled instance of the application and not inside Visual Studio.

RE: Report Sharp-Shooter slow preview process

Ivan George Borges — Mon, 10 Nov 2008 09:38:34 GMT

Glad it helped, Edhy! :cool:

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Mon, 10 Nov 2008 09:27:05 GMT

Hi Ivan,

Thanks a lot for taking the next step and provide the sample. It is clear now how it is done, without the sample and the poor help file it would be very difficult for me to really understand this.

Now I can have my own WinRenderDialog wich looks more professional and in synch with the application's look and feel.

Thanks again!

RE: Report Sharp-Shooter slow preview process

Ivan George Borges — Mon, 10 Nov 2008 05:33:33 GMT

Yeah, I saw the errors first time I run that sample, but as you said, it was probably a version problem, so you just had to implement the IRenderSite again and all the methods would be created.

Anyway, I used Trent's report sample and added a RenderSite class to it.

Hope it helps.

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Sat, 08 Nov 2008 16:43:22 GMT

Hi Ivan,

Thanks for the link, I had downloaded this project sample before, but it did not work for me. It is in C# and it is generating 2 compiled errors, which I believe are caused because the project was created for an old version of RSS.

I posted a request for a new sample from them, so let's see. Thanks anyway for keeping this in mind.

RE: Report Sharp-Shooter slow preview process

Ivan George Borges — Sat, 08 Nov 2008 08:39:57 GMT

[quote]I still need to figure out how to create my own class to implement the IRenderSite, but know that the report is rendering as it should, I will have more time to get that done.[/quote]
Hi Edhy.

Here you have a sample for the IRenderSite:

http://www.perpetuumsoft.com/ForumTopic.aspx?lang=en&topic_id=356&forum_id=9

RE: Report Sharp-Shooter slow preview process

Greg McGuffey — Fri, 24 Oct 2008 17:23:59 GMT

Nice!

RE: Report Sharp-Shooter slow preview process

Trent L. Taylor — Fri, 24 Oct 2008 17:23:12 GMT

Cool :cool:

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Fri, 24 Oct 2008 16:25:07 GMT

Once again, Trent, Greg thanks a lot.

The slowness was caused by the Custom Field Properties I had in the main BO, following your suggestions I did the following to overcome this issue:

Make my main BO CFP overridable.
In the report's BO I override the CFP using the logic suggested by Greg to create a temp BO with all the lookup data then seek the one requested by the report.

Here is some of my code used:

[codesnippet][code]

''' <summary>

''' Use to hold the Appliance Lookup BO

''' </summary>

''' <remarks></remarks>

Private _ApplianceBOLookup As New Business.bizAppliances

''' <summary>

''' The first time this property is used, it will check the

''' lookup BO count > 0 if not it will load all the records

''' Then will use the BO.SeekToPrimaryKey to speed up finding

''' the description value.

''' This method is much more faster than using a Custom Field Property in reports.

''' </summary>

''' <remarks></remarks>

Public Overrides ReadOnly Property [ApplianceName]() As System.String

Get

If _ApplianceBOLookup.Count = 0 Then

_ApplianceBOLookup.FillAllRecords()

End If

If _ApplianceBOLookup.SeekToPrimaryKey(Me.FK_Appliances) Then

Return _ApplianceBOLookup.ApplianceName

Else

Return String.Empty

End If

End Get

End Property

[/code][/codesnippet]

With the above code the time to render the report was reduced from 37 seconds to 3 seconds. I still need to figure out how to create my own class to implement the IRenderSite, but know that the report is rendering as it should, I will have more time to get that done.:cool:

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Fri, 24 Oct 2008 09:38:57 GMT

[quote][b]Trent L. Taylor (10/24/2008)[/b][hr]but I am not sure what this would buy you. It isn't going to make an performance differences.[/quote]

Overridable would work in my case, since I will refactor those Custom Properties but since they are used in some forms I don't want to redo the whole thing, I want to refactor them for the report only, to see if I can gain some speed in the process.

RE: Report Sharp-Shooter slow preview process

Trent L. Taylor — Fri, 24 Oct 2008 09:18:11 GMT

You could just make the properties on the base BO overridable and then override them in the report BO. You coudl then apply the <Browsable(False)> and <Bindable(False)> attributes and possibly several others to make them hidden, but I am not sure what this would buy you. It isn't going to make an performance differences. And if your end-user cannot modify the report, then this would be a non-issue.

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Fri, 24 Oct 2008 08:24:53 GMT

Thanks Trent, I will keep digging in the issue.

One more question if you don't mind :) I have some Custom Field Properties (CFP) in the main BO, which when using the BBS builder, will create a report bo inheriting the main bo and then will have those CFP, is there any way to remove those CFP from the report BO?

RE: Report Sharp-Shooter slow preview process

Trent L. Taylor — Fri, 24 Oct 2008 08:12:01 GMT

Well, this would be a pretty involved post. But in short, it is pretty much like I mentioned it. The IRenderSite implementation just allows you to create a hook and call it when you call the Prepare method on the ReportSlot. It also has events that you can handle indicating what is going on (i.e. compiling scripts, etc.) so that you can notify your end-user of the status. I am about to walk into a developer session and will be offline for a while, but if you don't make any progress let me know and I will try and give some more details.

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Thu, 23 Oct 2008 17:23:28 GMT

[quote]Also, are you show a "generating report dialog" while RSS is generating the report? They have one by default that you can show[/quote]

Trent, I have been looking in RSS help file and could not find any reference to this dialog or how to make my report use the default one. RSS site seems to be down, if possible could you tell me how to make use of the default dialog?

RE: Report Sharp-Shooter slow preview process

Edhy Rijo — Thu, 23 Oct 2008 17:21:29 GMT

Thanks Greg, will start testing with those approaches to see which one give me the best result.

RE: Report Sharp-Shooter slow preview process

Greg McGuffey — Thu, 23 Oct 2008 15:20:04 GMT

Edhy,

When doing look-ups similar this, I don't (very often) get the data within in the property any more. If I do, then I'd typically use a scalar method (not a fill method) instead. Instead, I fill the BO with the lookup values when it is loaded...one trip in that case. Here's sort of how I might do it:

[codesnippet]Public Sub FillForReport()
  '-- I'll use SQL statement here, but you could put into sproc to speed up
  ' Because the CustomerBuildingAddress is an agreggate of three fields,
  ' I'd probably do three custom properties in the BO and then aggregate
  ' them when needed (in the report), which provides more flexibility...
  ' easy to put the fields together, harder to rip them apart
  Dim sqlBuilder As New StringBuilder(255)
  With sqlBuilder
    '-- No idea what your SQL would look like...
    '   This just grabs all the fields of the BOs table, plus adds a City column
    '   via a sub query
    .AppendLine("Select *")
    .AppendFormat(" , (Select City From tblBuildingAddress Where BuildingID = {0}) As BuildingCity", Me.FK_Buildings).Append(ControlChars.NewLine)
    .AppendFormat("From {0}", Me.TableName).Append(ControlChars.NewLine)
  End With
  Using cmd As New SqlCommand()
    cmd.CommandText = sqlBuilder.ToString()
    Me.FillDataTable(cmd)
  End Using
End Sub

'-- One of three custom properties (City, Street, State..maybe add in ZIP too).
Public Readonly Property CustomerBuildingCity As String
  Get
    '-- Establish return var, provide empty string as default
    Dim city As String = String.Empty

    '-- Check if the city column was included in the data table
    '   If it is, retrieve value, otherwise, the default is returned
    If Me.CurrentDataTable.Columns.Contains("BuildingCity") Then
      city = CType(Me.CurrentRow.Items("BuildingCity"),String)
    End If

    '-- Return city
    Return city
  End Get
End Property[/codesnippet]

Trent has shown an alternative way (which is probably faster) for handling the use of an extra column in the data table to provide the value for a custom property. He uses a method that checks for any extra columns that might be used by custom properties and if the extra column isn't found, just adds it with a default value.

The advantage of doing it this way is you can drastically reduce the number of calls to the db. If your report returns 100 records, using the ExecuteScalar or worse, a Fill method within a customer property would result in (at least) 101 calls to the db (one for the original fill, and one for each time the custom property is called). Compare that with just one call using the above method.

Hope that makes sense. :D