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:
Based on the assumption above my studies on performance related to heavy reports have been the following:
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:
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:
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:
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
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.
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)
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.
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.
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.
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.
@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
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?)
I cannot promise anything but NAV Development team is aware of the performance picture and working on it.