Report performance issue - time in processing

Report performance issue - time in processing

• SeanC

  Hi,

  Please could you clarify what solution you ended up with In your earlier posting you mentioned using an assembly: what mixture of assembly and 'code block in the report' did you go with

  I'm interested to find out if you ended up with the cache in the code block & a whole bunch of access functions, or whether you managed to have it all in the assembly somehow.

  Rich

  
  

• talay

  The more grouping and nesting you have in the report, the longer it takes to process. With 2100 rows, calculations are made on each cell and row and group. More data means more calculations and the longer it takes to process the report.

  Reducing the grouping and or data will definately help performance.

  The RS team is working on ways to improve this in future products.

  
  
  

• Murtala Adewale

  If their code goes back to a file each time it's called, that is horribly inefficient. It makes for a simple example, however.

  Have you tried caching your lookups from the stylesheet Ie, something like this:

  Shared Dim stylesheet as System.Collections.Hashtable = New System.Collections.Hashtable()

  Shared Function GetBackgroundColor() As String
  If stylesheet("BackgroundColor") Is Nothing
  stylesheet("BackgroundColor") = /* lookup the value here */
  End If
  Return stylesheet("BackgroundColor")
  End Function

  If you go this route, note that if you want different templates per report, you'll need to build that into your logic. Also, pay special attention to the Thread Safety section of the Hashtable documentation as multiple writer threads are not automatically thead-safe.

  
  
  

• B. Horwatt

  Running filemon demonstrated that the code was indeed going back to the file each time it's called. A quick trial demonstrated that simply caching the XML stylesheet file on first load and then accessing it from memory made a huge difference.

  Thanks for you help with this.

  
  

• Dave Corun

  I've experiemented with reduced levels of grouping but this made very little difference - report still takes 20 minutes. What has made a huge difference is stripping out the code functions which retrieve formatting from a stylesheet - with hardcoded formatting the report generates in 35 seconds. We have a requirement to implement variable formatting based on a stylesheet or something similar - is there any way of implementing this without incurring such a huge overhead

  Surely something as basic as dynamic formatting must be a frequent requirement Is anything planned for future releases

  
  

• sk49

  Each textbox property for Font, text alignment, background colour etc are set by an expression which calls a coded assembly function eg

  =Code.StyleSheetClass.Style("StyleTemplatename.StyleName.FontFamily#Tahoma")

  where StyleTemplateName and StyleName are entries in a customised version of the RS styletemplates.xml. The code is therefore loading the stylesheet and retrieving the relevant properties from the stylesheet for the styletemplate and style passed in to use as the report item properties. It's basically a copy of the method demonstrated in Ch9 of the Hitchhiker's Guide to SQL Server 2000 Reporting Services book by Peter Blackburn and William Vaughn.

  This particular report layout is very simple - a single 10 column table with 3 groups to display and aggregate various daily values per month, grouped type retrieved within a date range selected; but it does return > 2000 rows and all items (table header/footer, group headers/footers, row headings, table detail cells etc all retrieve properties retrieved from the stylesheet.

  Any guidance to a more efficient method of achieving dynamic formatting Is a stylesheet type implementation likely in future releases It seems terribly restrictive to be limited to a template based model when frequently a client changes their mind after development about styles, or they want to reuse reports for a different project with a different formatting flavour.

  
  

• Gogou

  To judge the cost of dynamic formatting, replace the calls to your method with a simple expression like ="Red" or ="Bold" or ="10pt". Or even, =switch(Fields!Field.Value == "Value1", "Red", Fields!Field.Value == "Value2", "Blue", true, "Green")

  If your code is loading a stylesheet, querying it, and returning a value very frequently (like for multiple properties per textbox), this can be very slow. Can you elaborate a bit on what your code is doing

  
  
  
• Report performance issue - time in processing