Chars of accounts performance on NAV 2013

clarasdk

After updating a system from NAV 2009 R2 to NAV 2013 we are suddenly experiencing mayor performance problems when opening the chart of account view. (Around 8 sec’s to open and 8 secs load time for every page down). More or less the pages is not useable.

As far as we know the changes made to SIFT in NAV 2013 should improve performance but here this does not seem to be the case…. Using the SQL profiler is seems that NAV is now generating one big script to calculate the SIFTs using OUTER APPLY per calculated field. Where NAV 2009 seemed to fire of individual sum commands to the SQL server.

Just for info there is around 800 accounts in the table and around 340.000 entries in the G/L Entry table spread over 15 years. So nothing really big…. And I therefore wonder if this is a problem with only our system or a general problem. I would have thought that the Chart of accounts would be one of the main areas for MS to do performance testing and that they would have noticed such a problem.

We have analyzed all the indexes and they are fully optimized and the sifts are also activated (deactivating them has also been tried but without any positive effect)
On NAV 2009 R2 RTC the Chart of accounts work ok (maybe 1-1.5 sec delay to open).

Does anybody else have the same experience with NAV 2013? Any solutions/work around?

kriki

Did you try to rebuild (NOT defrag) the indexes first?

Marije_Brummel

There is an update available for the flowfields.

I've experienced the same issues with other tables. It seems that new ways of indexing are required now with covering indexes.

I would look for the update first and if that fails look at the indexes and off course escalate to Microsoft.

clarasdk

kriki wrote:

Did you try to rebuild (NOT defrag) the indexes first?

I have deactivared and reactivated the maintain sift and sql index from NAV. Bit that did not help anything. Is there something else I need to do to rebuild them?

clarasdk

Mark Brummel wrote:

There is an update available for the flowfields.

I've experienced the same issues with other tables. It seems that new ways of indexing are required now with covering indexes.

I would look for the update first and if that fails look at the indexes and off course escalate to Microsoft.

We currently have the solution on one of the latest hotfix versions. Will check the precise version tomorrow. Is this flowfield fix a fairly new fix? Can you remember the number?

I think it is pretty bad if we have to begin changing indexes in a standard table being used for a standard page just to make it work I would believed that MS would test such an vital area with more than 5 records in the g/l entry tabel.....

Marije_Brummel

I don't think there are many large 2013 databases out there. I have one that is 60GB and I had enough issues with this outer apply thing and still some to solve that are slow, but not slow enough to spend time on.

Looks like MS has changed the SQL business again with this change.

Can you post the query that is slow? Or send it by email? Actually some of them queries are 3 pages long and when in profiler it takes a few seconds to show and 15 minutes to decompile.

clarasdk

Hi Mark

I will gather some stuff and send to you later today.

I have cleaned up the SQL statement that is done when opening the chart of accounts table so that I can run it directly in SQL management studio and the query that is create by NAV takes 8.5 sec on it own...

I can see that they are now using outer apply instead of outer joins, I wonder if that is good for performance.

I also think that all flowfields are calculated even when they are not shown on the page, that is also something I need to check today.

I found something about flowfield fix in nav2013 roleup 2, but as we are on roleup3 I think that should be included in our version, so that is not the solution at least.

clarasdk

Hi

Just had a break through!

I found out that the property "SQL DATA TYPE" on the No. field in table 15 was set to integer.

After changing it to default (undefined) everything is back to fast (very fast actually).

I checked another area of the solution where we use this property and we actually also have reduced performance there.

I have now reported the issue to MS.

clarasdk

Just wanted to give the precise solution to the problem we had.

We had the SQLDataType property set to Integer in the No. field in table 15. In NAV 2009 this never gave a problem. But with the way NAV 2013 is doing the SQL statement (with a big outer apply statement) this leads to typecasting problems as the sift table still has the field in the key as a varchar. We had the same problems in table 36 and 37 + 112 and 113 were we on one customer also had the sorting set to integer.

So the solution is to also set the property in the field that is the source for the sift calculation. In this case, it means that the G/L Account No. field in Table 17 also need to have the property set to integer. Then the performance is good again.
Therefore, what you need to be aware of is that this does not happen automatically and that now this can have a performance impact.

I hope this can be helpful to others view similar problems in the future.

Feel free to contact me if you need the scenario elaborated.

Marije_Brummel

I ran into this issue while doing a large upgrade to NAV2013R2 from NAV5.

After communication with Claus and Microsoft here are my findings:

http://markbrummel.wordpress.com/2014/0 ... er-issues/

Thanks for your time Claus.

mdPartnerNL

1.
Maybe a solution is to add an integer sort field in these tables. And then sort the page/report on that field or would that slow down other functions?

2.
Another permanent solution..

Would writing a sql script to add extra zeroes (depending on the largest value ..for example 9999...) in front of a lower value ..for example 12 to 0012... be possible?

Per table:
- It first should look at keyfields with varchar en check if all values are numeric, then add the "pre-zeroes"

Marije_Brummel

I did option two.

This project is a 100+GB database with 12 companies. Large scripts that run forever. 8)

mdPartnerNL

Mark Brummel wrote:

I did option two.

This project is a 100+GB database with 12 companies. Large scripts that run forever. 8)

Is it a generic script? or does it have to be specially made for every table?

Marije_Brummel

Specially made for each table. I have a NAV Codeunit creating the script based on the field and object table.

vasja

We have same issue but only if we use flow filters. When flow filters are not used on chart of accounts then all is OK. But if we enter flow filter for, say, posting date, then system goes very slow - 8 to 20 sec to do inital page, then after every page down it takes 10 sec. Database is small - 5GB. All fields has no SQL data type defined at all.

App server has no CPU at all during this, but SQL server does. I looked in SQL profiler and query which takes long time is this:

declare @p1 int
set @p1=1973
exec sp_prepexec @p1 output,N'@0 binary(8),@1 nvarchar(20),@2 nvarchar(250),@3 int,@4 tinyint,@5 int,@6 datetime,@7 decimal(38,20),@8 datetime,@9 datetime,@10 datetime,@11 datetime,@12 datetime,@13 datetime,@14 datetime,@15 datetime,@16 nvarchar(20)',N'SELECT ISNULL("G_L Account"."timestamp",@0) AS "timestamp",ISNULL("G_L Account"."No_",@1) AS "No_",ISNULL("G_L Account"."Name",@2) AS "Name",ISNULL("G_L Account"."Search Name",@1) AS "Search Name",ISNULL("G_L Account"."Account Type",@3) AS "Account Type",ISNULL("G_L Account"."Global Dimension 1 Code",@1) AS "Global Dimension 1 Code",ISNULL("G_L Account"."Global Dimension 2 Code",@1) AS "Global Dimension 2 Code",ISNULL("G_L Account"."Income_Balance",@3) AS "Income_Balance",ISNULL("G_L Account"."Debit_Credit",@3) AS "Debit_Credit",ISNULL("G_L Account"."No_ 2",@1) AS "No_ 2",ISNULL("G_L Account"."Blocked",@4) AS "Blocked",ISNULL("G_L Account"."Direct Posting",@4) AS "Direct Posting",ISNULL("G_L Account"."Reconciliation Account",@4) AS "Reconciliation Account",ISNULL("G_L Account"."New Page",@4) AS "New Page",ISNULL("G_L Account"."No_ of Blank Lines",@5) AS "No_ of Blank Lines",ISNULL("G_L Account"."Indentation",@5) AS "Indentation",ISNULL("G_L Account"."Last Date Modified",@6) AS "Last Date Modified",ISNULL("G_L Account"."Totaling",@2) AS "Totaling",ISNULL("G_L Account"."Consol_ Translation Method",@3) AS "Consol_ Translation Method",ISNULL("G_L Account"."Consol_ Debit Acc_",@1) AS "Consol_ Debit Acc_",ISNULL("G_L Account"."Consol_ Credit Acc_",@1) AS "Consol_ Credit Acc_",ISNULL("G_L Account"."Gen_ Posting Type",@3) AS "Gen_ Posting Type",ISNULL("G_L Account"."Gen_ Bus_ Posting Group",@1) AS "Gen_ Bus_ Posting Group",ISNULL("G_L Account"."Gen_ Prod_ Posting Group",@1) AS "Gen_ Prod_ Posting Group",DATALENGTH("G_L Account"."Picture") AS "Picture",ISNULL("G_L Account"."Automatic Ext_ Texts",@4) AS "Automatic Ext_ Texts",ISNULL("G_L Account"."Tax Area Code",@1) AS "Tax Area Code",ISNULL("G_L Account"."Tax Liable",@4) AS "Tax Liable",ISNULL("G_L Account"."Tax Group Code",@1) AS "Tax Group Code",ISNULL("G_L Account"."VAT Bus_ Posting Group",@1) AS "VAT Bus_ Posting Group",ISNULL("G_L Account"."VAT Prod_ Posting Group",@1) AS "VAT Prod_ Posting Group",ISNULL("G_L Account"."Exchange Rate Adjustment",@3) AS "Exchange Rate Adjustment",ISNULL("G_L Account"."Default IC Partner G_L Acc_ No",@1) AS "Default IC Partner G_L Acc_ No",ISNULL("G_L Account"."Cost Type No_",@1) AS "Cost Type No_",ISNULL("G_L Account"."Acc_ Type Assets Liabilities",@3) AS "Acc_ Type Assets Liabilities",ISNULL("G_L Account"."Require Duble Posting",@4) AS "Require Duble Posting",ISNULL("G_L Account"."DP Account No_",@1) AS "DP Account No_",ISNULL("G_L Account"."DP Balance Account No_",@1) AS "DP Balance Account No_",ISNULL("SUB$Debit Amount"."Debit Amount$G_L Entry$SUM$Debit Amount",@7) AS "Debit Amount",ISNULL("SUB$Credit Amount"."Credit Amount$G_L Entry$SUM$Credit Amount",@7) AS "Credit Amount",ISNULL("SUB$Net Change"."Net Change$G_L Entry$SUM$Amount",@7) AS "Net Change",ISNULL("SUB$Balance"."Balance$G_L Entry$SUM$Amount",@7) AS "Balance",ISNULL("SUB$Net Change (VAT Date)"."Net Change (VAT Date)$G_L Entry$SUM$Amount",@7) AS "Net Change (VAT Date)",ISNULL("SUB$Balance at Date"."Balance at Date$G_L Entry$SUM$Amount",@7) AS "Balance at Date",ISNULL("SUB$Additional-Currency Net Change"."Additional-Currency Net Change$G_L Entry$SUM$Additional-Currency Amount",@7) AS "Additional-Currency Net Change",ISNULL("SUB$Net Change ACY (VAT Date)"."Net Change ACY (VAT Date)$G_L Entry$SUM$Additional-Currency Amount",@7) AS "Net Change ACY (VAT Date)",ISNULL("SUB$Add_-Currency Balance at Date"."Add_-Currency Balance at Date$G_L Entry$SUM$Additional-Currency Amount",@7) AS "Add_-Currency Balance at Date",ISNULL("SUB$Additional-Currency Balance"."Additional-Currency Balance$G_L Entry$SUM$Additional-Currency Amount",@7) AS "Additional-Currency Balance" FROM "Avala".dbo."Avala$G_L Account" AS "G_L Account" WITH(READUNCOMMITTED) OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Debit Amount$G_L Entry"."SUM$Debit Amount"),@7) AS "Debit Amount$G_L Entry$SUM$Debit Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Debit Amount$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Debit Amount$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Debit Amount$G_L Entry"."Posting Date",@6)<=@8)) AS "SUB$Debit Amount" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Credit Amount$G_L Entry"."SUM$Credit Amount"),@7) AS "Credit Amount$G_L Entry$SUM$Credit Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Credit Amount$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Credit Amount$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Credit Amount$G_L Entry"."Posting Date",@6)<=@9)) AS "SUB$Credit Amount" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Net Change$G_L Entry"."SUM$Amount"),@7) AS "Net Change$G_L Entry$SUM$Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Net Change$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Net Change$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Net Change$G_L Entry"."Posting Date",@6)<=@10)) AS "SUB$Net Change" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Balance$G_L Entry"."SUM$Amount"),@7) AS "Balance$G_L Entry$SUM$Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Balance$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Balance$G_L Entry"."G_L Account No_"="G_L Account"."No_")) AS "SUB$Balance" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Net Change (VAT Date)$G_L Entry"."Amount"),@7) AS "Net Change (VAT Date)$G_L Entry$SUM$Amount" FROM "Avala".dbo."Avala$G_L Entry" AS "Net Change (VAT Date)$G_L Entry" WITH(READUNCOMMITTED) WHERE ("Net Change (VAT Date)$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Net Change (VAT Date)$G_L Entry"."VAT Date",@6)<=@11)) AS "SUB$Net Change (VAT Date)" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Balance at Date$G_L Entry"."SUM$Amount"),@7) AS "Balance at Date$G_L Entry$SUM$Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Balance at Date$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Balance at Date$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Balance at Date$G_L Entry"."Posting Date",@6)<=@12)) AS "SUB$Balance at Date" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Additional-Currency Net Change$G_L Entry"."SUM$Additional-Currency Amount"),@7) AS "Additional-Currency Net Change$G_L Entry$SUM$Additional-Currency Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Additional-Currency Net Change$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Additional-Currency Net Change$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Additional-Currency Net Change$G_L Entry"."Posting Date",@6)<=@13)) AS "SUB$Additional-Currency Net Change" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Net Change ACY (VAT Date)$G_L Entry"."Additional-Currency Amount"),@7) AS "Net Change ACY (VAT Date)$G_L Entry$SUM$Additional-Currency Amount" FROM "Avala".dbo."Avala$G_L Entry" AS "Net Change ACY (VAT Date)$G_L Entry" WITH(READUNCOMMITTED) WHERE ("Net Change ACY (VAT Date)$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Net Change ACY (VAT Date)$G_L Entry"."VAT Date",@6)<=@14)) AS "SUB$Net Change ACY (VAT Date)" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Add_-Currency Balance at Date$G_L Entry"."SUM$Additional-Currency Amount"),@7) AS "Add_-Currency Balance at Date$G_L Entry$SUM$Additional-Currency Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Add_-Currency Balance at Date$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Add_-Currency Balance at Date$G_L Entry"."G_L Account No_"="G_L Account"."No_" AND ISNULL("Add_-Currency Balance at Date$G_L Entry"."Posting Date",@6)<=@15)) AS "SUB$Add_-Currency Balance at Date" OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Additional-Currency Balance$G_L Entry"."SUM$Additional-Currency Amount"),@7) AS "Additional-Currency Balance$G_L Entry$SUM$Additional-Currency Amount" FROM "Avala".dbo."Avala$G_L Entry$VSIFT$1" AS "Additional-Currency Balance$G_L Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Additional-Currency Balance$G_L Entry"."G_L Account No_"="G_L Account"."No_")) AS "SUB$Additional-Currency Balance" WHERE (ISNULL("G_L Account"."No_",@1)>@16) ORDER BY "No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)',@0=0x0000000000000000,@1=N'',@2=N'',@3=0,@4=0,@5=0,@6='1753-01-01 00:00:00',@7=0,@8='2014-01-31 00:00:00',@9='2014-01-31 00:00:00',@10='2014-01-31 00:00:00',@11='2014-01-31 00:00:00',@12='2014-01-31 00:00:00',@13='2014-01-31 00:00:00',@14='2014-01-31 00:00:00',@15='2014-01-31 00:00:00',@16=N'699999'
select @p1

vasja

This is old topic but I have to make an update to this problem. While trying to solve slow performance caused by flow fields, I found out that no matter what you put on the page, SQL query that goes to SQL server is the same! This query always takes ALL FIELDS from SQL server no matter what you put on the page. So if you want to remove flow fields from the page to make it faster, it will not help at all, because program will calculate those flow fields anyway.