RDLC Report and Performance in Microsoft Dynamics NAV

RDLC Report and Performance in Microsoft Dynamics NAV

Rate This
  • Comments 9

It has been a while since I last blogged and I am taking the chance now to post on a very delicate argument. The main focus of my dissertation is about performance (Out Of Memory exception, typically) with Microsoft Dynamics NAV 2009 R2, Microsoft Dynamics NAV 2013, and Microsoft Dynamics NAV 2013 R2.

I would encourage you to post your comments and thoughts. Have a good read.

Microsoft Dynamics NAV 2009 R2 Considerations (RDLC 2005 - Report Viewer 2008)

All the Microsoft Dynamics NAV 2009 stack (RTM, SP1, R2) is built and sealed for the x86 platform. This means that both client (Microsoft.Dynamics.NAV.Client.exe) and server (Microsoft.Dynamics.NAV.Server.exe) are 32bit components of the NAV platform. RDLC Reporting (Enhanced Reporting, in the recent NAV terminology) in Microsoft Dynamics NAV 2009 is made of a Report Viewer .NET Control targeted for WinForm, and Microsoft Dynamics NAV casts this control into a Microsoft Dynamics NAV modal page (that is a WinForm, roughly speaking) within the RTC boundaries.

Report Viewer works, in principle, accepting 2 items:

-          a metadata definition plain XML file (Report.rdlc) that define the structure of the report rendering runtime  

-          a Dataset that is a serialized XML file that contains the data to be rendered in the way defined in the rdlc file definition

With Microsoft Dynamics NAV 2009, Report Viewer works client-side to render the report to the user (Preview Layout rendering extension) and therefore it needs to have both RDLC definition and dataset streamed completely from the Server to the Client. This streaming process of Client memory population, since Microsoft Dynamics NAV 2009 SP1, is made with a chunking method that can be resumed in shorts as per below.

SQL Server process and generate a complete Result Set. The Result Set is sent to the Microsoft Dynamics NAV Server as normal TCP packets informations and the Microsoft Dynamics NAV Server, meanwhile receiving these packets from SQL Server, is sending this Result Set in chunks to the client, clearing the Microsoft Dynamics NAV Server memory once the packet is received from the client. This has been introduced to avoid memory leak server side that works only as routing point for packets / chunks from SQL Server to the Microsoft Dynamics NAV Windows client. If you open task manager both in the Middle Tier machine and Client machine, meanwhile processing a Heavy report (or whatever report), you might notice that the memory footprint server side is constant and pretty low while the Client one is growing and growing in consumption until it reaches a physical limit.

When it reaches its physical limit, you receive the typical error message like the one shown below (explicit Out Of Memory exception)

And, most of the times, report viewer continue clearing the error message and simply display a single blank page (implicit Out Of Memory exception) or several pages with mixed random string value assignments (blurred Out of Memory exception).

I do not want to go more deep into the technicalities that lies beneath but you have to consider the following:

  1. The Microsoft Dynamics NAV 2009 R2 Role Tailored client is a 32bit application (with a limit, on the chart, of 2GB memory per process).
  2. The Microsoft Dynamics NAV 2009 R2 Role Tailored client and report(s) share the same memory Application Domain (this means the same memory stack).
  3. Report Viewer control run in a sort of sandbox mode inside the Microsoft Dynamics NAV WinForm so that the memory consumption is even more limited (approx. 1GB).

Based on the assumption above my studies on performance related to heavy reports have been the following:

  1. Report Viewer Preview rendering extension within Role Tailored Client is raising an Out Of Memory exception when Client process memory reaches 0.8 – 1.1 GB approx. (this differs between multiple factors like e.g. OS, Hardware type, Resources, etc.)
  2. Considering a typical Microsoft Dynamics NAV dataset (60 – 80 columns on average) there is a potential risk of Out Of Memory between 40K up to 100K rows range. This depends on number of columns in the dataset and quality of columns (e.g. which data type they belongs, if and how this is populated, etc.).

If you pack up all these considerations, these are the actions that you might take (or have to) depending on your scenarios within the Microsoft Dynamics NAV 2009 R2 stack:

  1. If your report is raising an Out Of Memory exception in a range lower or close to 80/90K rows then you can try to optimize the report by reducing the Dataset. Reducing the dataset means :
    1. Write optimal code for RDLC Report (e.g. use CurrReport.SKIP when needed, avoid use data items for CALCSUMS and use record AL variables instead, rewrite the report to use drill-through to enable getting to details if required in the report - so still possible to move calculations to CSIDE – or refactor to use hyperlink to another report for details, etc.)
    2. Reduce the Dataset Columns (e.g. eliminate Section control that you do not use with RDLC report)
    3. Reduce the Dataset Rows (refactor as much as it possible to push in the dataset only the data that need to be printed)
  2. If your report is already in a range equal or higher then 80/90K then you have no other choices with NAV 2009 R2 than the following :
    1. Delete RDLC Report layout and enable Classic Client report fall back (this is the solution that I will warmly suggest and it is a really finger snap solution)
    2. (this is pretty obvious) Apply filters in the request page (or through AL Code) in order to reduce the amount of rows in the dataset and instead of print the report in one single shot, print it N times.

And this is all about the Microsoft Dynamics NAV 2009 R2 stack and how to solve / workaround the problem in the feasible (and easiest way) within this version.

Microsoft Dynamics NAV 2013 (RDLC 2008 – Report Viewer 2010) / NAV 2013 R2 (RDLC 2010 – Report Viewer 2012) is another story and challenge type.

To resume, the milestone changes between Microsoft Dynamics NAV 2009 and Microsoft Dynamics NAV 2013 (and R2) are the following:

  1. Microsoft Dynamics NAV Server is now 64bit (finally…) while the Windows client still remains as 32bit application. This means that the client is still a physical bottleneck and are still valid the considerations related to memory footprint and dataset volume as reported previously for Microsoft Dynamics NAV 2009 R2.
  2. You cannot anymore enable Classic client report fallback but you have to use RDLC Report in any occasion.

With these 2 new variables or constraints in mind, below how you could workaround / resolve the performance problem with Microsoft DynamicsNAV 2013  / Microsoft Dynamics NAV 2013 R2:

  1. Same considerations about Optimizing reports: if you receive (or think of receiving) an Out Of Memory exception you might go for optimize the report as much as you can IF you forecast that in the end your dataset will never ever exceed 70/90K rows.
  2. If you have heavy reports with a dataset volume higher than 70/90K rows then this is what you could do:
    1. Filter data and print the report N times, wherever possible (use common sense)
    2. Use the Job Queue to enable Server Side Printing. What is Server Side Printing? It is simply running Report Viewer totally in the background through NAS Services (that is using Background Sessions through STARTSESSION AL statement). Running Server Side means running under 64 bits context and therefore Report Viewer (“.NET component targeted for any CPU” = 64 bit enabled) will use ALL the memory available from the OS (e.g. if you have 32 GB it could reach up to consume all of these if you have to work with several MILLION of dataset rows – I have seen it with my own Italian eyes - ) and you will succeed in PRINT the report or, better, use SAVEASPDF to generate a PDF file to be consumed by the user.
    3. Use STARTSESSION AL statement as you like in your own custom code after gathering user filters and parameter and pass this to a Codeunit that does filter record(s) and run a SAVEASPDF in the background as per your exotic flavor.

THE FUTURE

The Microsoft Dynamics NAV Core team is fully aware about these scenarios and working hard on improving the RDLC Report performance and experience in future versions of Microsoft Dynamics NAV

NOTE:

In this blog post you will find a set of objects (1 Report, 1 Codeunit, 1 Page) to easily simulate an Out Of Memory exception or Save as PDF the report in background.

Just import these NAV object set and run Page 50666. You can choose to simulate an Out Of Memory exception by clicking the appropriate Action and then Preview the Report or you can choose to SAVEASPDF the same Report via enabling a Background Session that would do this action Server Side. 

Be sure to have at least 4 GB of Available Memory Server Side and just wait for the background session to end its activity and stream out the content of the report (this should take close to 5/6 minutes with a standard Cronus database, depending on resources).

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Duilio Tacconi (dtacconi)

Microsoft Dynamics Italy

Microsoft Customer Service and Support (CSS) EMEA

A special thanks to Peter Borring Sørensen & Torben Wind Meyhoff from the Microsoft Dynamics NAV core team.

Attachment: MicrosoftDynamicsNAV_BlogRDLCandPerformance_BackgroundSaveAsPdf.fob
Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • Just in case you do not receive an Out Of Memory from the object, just apply what is written :

    a. Increase the number of rows (elevate the second value for SETRANGE(Number,1,NNNNN) or add more customers)

    and/or

    b. Increase the numbr of columns (add more Column in Dataset Designer).

    The "spirit" of this blog is to inspect Background Session as a productive alternative to long-running reports.

  • Another optimization I could suggest is assure the company logo is printed on just one line of dataset: I experienced in some cases (especially with quite big images) this simple modification can remarkably reduce the dataset size.

  • Hi Daniele,

    yes. This is also a typical optimization that is meant to change the quality of the field (contains a BLOB or is empty). The typical change for e.g. R.206 is to CLEAR(CompInfo.PICTURE); after the first Sales Invoice Line processed in order to avoid useless BLOB images in subsequent invoice(s) line.

  • Hi,

    Any plans to rewrite the worst out of the box reports like inventory valuation? I have had to rewrite it to follow your rules, but it seems like it would be better for your team to do it.

    The report sends all the detail to the layout, then the layout sums and only prints the group totals.

    Regards,

    Dave

  • Hi Duilio,

    There is another fix that MS gave me a while back that you don't mention.

    Setting the NetFx40_LegacySecurityPolicy in the Microsoft.Dynamics.Nav.Client.exe.Config file to true also fixes the memory error for hotfix 346519 and above.

    Regards,

    John Collins

  • @Dave I think that if you download the latest UR for NAV 2013, Report 1001 should be fixed and no VE records are pushed to the dataset. In any case, SE Team is aware of this bad design related to it.

    @John Even if it is good practice to go for what is written in this blog blogs.msdn.com/.../memory-usage-in-microsoft-dynamics-nav-2013-print-preview.aspx

    you will Always hit a limit (Client is 32bit application) and for very heavy reports you can only fall back to server side Printing. I encourage you to try out my sample by incrementing the SETRANGE(Number,1,xxxx) where xxxx is something like e.g. 9-10K. You will see that Client will go OOM while server side it will grow up to 4/6 GB and generate PDF file afterwards.  

  • @Dave Just one small correction about my last Verbatim. Changes to R.1001 are still not included in any CU for 2013 or 2013 R2 but they are working on haveing this included too. Just FYI

  • Thanks Duilio,

    I use this to create a 500Mb pdf file related to s sale price list with thousands of pictures. It works fine. But do you think we'll see this feature in standard report actions in next Nav release (I mean "Print in background" and "Pdf in background" in the standard action of a report request page?)

    Ciao

    Stefano  

  • I cannot promise anything but NAV Development team is aware of the performance picture and working on it.

Page 1 of 1 (9 items)