And let me start by saying that in the evidence of all data so far: It is. Significantly.
Number of changes applied to Dynamics NAV 2013 architecture contribute to performance boosts many a test have shown. To outline some of them:
… and more.
However(!), a few things have surfaced in the course of time that are not as explicitely documented as the changes above, nor as apparent, and might have unexpected side effects. I have collected some of the usual side effects that you might or not be aware of and that might leave you panicking if not certain what you’re facing.
Consider the following example. This is just an illustration of the problem, constructed on CRONUS extended database:
A lot of Inventory transactions are posted through the Item Journal, generating a lot of Post cost to G/L Entry. After this, when browsing item list and opening Item card, the page opens very slowly.
After locating the offending query in the sql profiler and running it isolated in Microsoft SQL Server Management Studio with ‘Include Actual Execution Plan’ option, the plan looks similar to the one shown below:
Each sub-query shows reasonably (small) percentage of cost and no apparent reason for bad execution plan. There are no obvious extreme costs, however there is a Clustered Index Scan here:
Looking at the filter that SQL Server applies, namely it filters on “Post Value Entry to G_L”. “Item_No_” == “Item”.”No_”:
Although SQL Server reports Operator Cost as small, it shows CPU Cost in excess of 2.3 in approx. 1.94 executions. So it likely scans the table twice and unfortunately the table has 4,7 million rows.
And although it is not obvious from the actual SQL Server execution plan that this is a problem, profiling with the SQL Server Profiler reports the query to use more than 5 seconds of CPU, while doing 131.519 reads to fetch 52 rows:
The reason the Duration is on par with CPU Seconds is that all reads are logical from SQL Server Buffers. Re-issuing the query after adding the supporting index shows this in SQL Server Profiler:
So Reads reduced factor 100 and (warm) duration reduced factor 40.
As you see, these poor execution plans are not caused by SmartSQL, but the fact SmartSQL queries don’t cache will only amplify the issue. To solve it, we have to tackle the performance of that one isolated query by creating a covering index to improve execution plan.
And no, it won’t help to merely customize the page or change the visibility of the field. As long as it is contained in page metadata (so unless removed from page altogether), it will be calculated.
So in short, if you do run into issue of rather dramatic slowness of a page containing flowfields in Dynamics NAV 2013 or higher, isolating and testing flowfield queries separately (focusing on ones with clustered index scan, regardless of cost) should lead you to the culprit fairly quickly. A supporting index should resolve the problem.
However, maintaining all this can consume quite a lot of memory on your middle tier, so plan for ample when scaling. Also, as mentioned above, querying temp tables is cached but sorting these is a fairly expensive operation, just something to keep in mind.
You might (or not) be aware that the transaction scope and behavior of a page action has changed in Microsoft Dynamics NAV 2013 and higher. This will be especially significant if you are calling functions in codeunits (or objects other than the source table), passing the REC as parameter and intending to lock the record in that function.
Consider the following example: You have added an action that invokes a function in a codeunit, that in turn locks/ modifies the record (typically calling custom posting routine)
So OnAction trigger code is, for example, as follows:
Where PostingFunction is a function (in any object other than the source table).
Now the consequence of mentioned transaction scope change and the fact that you’re locking the record in the function, is that the entire source table (Rec) youpass as parameter in the example above, is locked. In other words, you’re passing the current record as a parameter, but you are locking the whole table.The behavior would cause more damage than good to change at this point due to all functionality it would affect, so it won’t be changed, but fortunately – ifyou’re aware of this issue, the solution is very simple:
SETSELECTINOFILTER(Rec); //adding this line
Adding the line above should reduce the scope of locking and leave you with locking just the one record (or selection of records if on a list). This applies to card pages as well.
When Dynamics NAV issues a query that returns thousands of rows, SQL Server will start a work thread to return the result. If application does not consume the rows as fast as they are delivered from SQL Server then the SQL Server work thread will have to wait and that shows up as wait type Network_Async_IO. If application never consumes the entire result set then the worker thread will be hanging until the transaction is ended (when we close the statement), or if it was a read transaction, for a longer period either until the connection is closed (as idle) or if the statement is overwritten by another statement (statement cache is full).
Example: when we do a FINDSET, a number of records (50, 100...) is retrieved. If it is a larger set it will run until all records are retrieved (or buffer is full), even if only first 10 are actually read by application. Eventually the session goes to sleep and after transaction ends sleeping sessions are removed. So in short, these are merely reflecting NAV data access methods, and are not a problem as such. If you want to reduce these, make sure you’re reading all the rows you’re asking for when using FINDSET, otherwise use FIND(‘-‘) or FIND(‘+’).
With thanks to Jesper Falkebo and Jens Klarskov Jensen
These postings are provided "AS IS" with nowarranties and confer no rights. You assume all risk for your use.