NAV Team: RDLC Report and Performance in Microsoft Dynamics NAV

[Blog bot]

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&hellip 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.



Источник: http://feedproxy.google.com/~r/Micro...amics-nav.aspx