As I already explained in my last blog post the way performance tuning is done in NAV2013R2 has changed from previous versions.

Rather than issueing a SQL Statement for each flowfield in each row on a listpage NAV now uses OUTER APPLY.

In a large upgrade project I am doing with a 100+GB database the Posted Invoices did not perform both from purchase and sales.

It is more work to analyse a query. Each flowfield adds an OUTER APPLY to the query and sometimes this can lead to queries being a page long. It helps to paste the query in SQL server management studio and search for OUTER APPLY and add a line break for each.

In this scenario the Amount and Amount Incl. VAT flowfields were added to the list hence two OUTER APPLY statements were added.

In this case the SIFT on the primairy key was disabled which was best practive in older versions of NAV.

Sift

However by activating the MaintainSIFTIndex property the performance of the page was back to being ultra fast like it should be in NAV2013R2.