Custom Flowfield that Cause Performance Problem

superpuz

Hi all,

I'm using NAV 2013 RTM, on Windows 2012 Standard Edition, and SQL Server 2008 R2 as it's DBMS.

I've been using ID (Indonesion) version of NAV since NAV 4.0 SP3. For NAV 2013 hasn't released in ID version, so I intended to adapt some of the functionality of ID localized version in NAV 2009 to NAV 2013.

In the process I found a performance problem while modifying Balance at Date field's calculate formula filter in G/L Account table.
The W1 table filter was:

G/L Account No.=FIELD(No.),G/L Account No.=FIELD(FILTER(Totaling)),Business Unit Code=FIELD(Business Unit Filter),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(UPPERLIMIT(Date Filter))

I changed it into:

G/L Account No.=FIELD(No.),G/L Account No.=FIELD(FILTER(Totaling)),Business Unit Code=FIELD(Business Unit Filter),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(UPPERLIMIT(Date Filter)),Entry Type=FIELD(G/L Entry Type Filter)

I added Entry Type=FIELD(G/L Entry Type Filter).

Of course I've added the Entry Type field on G/L Entry, and G/L Entry Type Filter on G/L Account table. Plus I've added a field to G/L Entry's secondary key (a key with SumIndexField specified, and one of the field that specified is Amount)

Originally, the key on W1 is

G/L Account No.,Global Dimension 1 Code,Global Dimension 2 Code,Posting Date

I changed it into this

G/L Account No.,Global Dimension 1 Code,Global Dimension 2 Code,Posting Date,Entry Type

The problems occurs when I ran the G/L Account table or Chart of Account Page. The page will be not responding for a long time only for showing the several first records. Scrolling the records of G/L Account will make the NAV Client Freeze for a couple minute.

FYI, I have about 2million records on the G/L Entry.

The magic is, when I returned the Calculate formula table filters to the original, this table performs just perfect (it seems no issue at all)

Is there anyone know what is it happening and how to workarround / solve this?

Thanks in advance.

superpuz

Upgrading the NAV platform solving the problem. I use Build 35001, the problem is gone.

mdPartnerNL

Is this the rollup version?

superpuz

Yes IF 'Platform Hotfix' is what you mean by that.

You can get the latest available Hotfix at:
https://mbs.microsoft.com/Cms/Templates/document/General.aspx?NRMODE=Published&NRNODEGUID={8CA1C4BF-A2B5-40E3-B0C4-A1E8569DBA40}&NRORIGINALURL=%2fpartnersource%2fsupport%2fselfsupport%2fhottopics%2fNAV2013PlatformHotfixOverview&NRCACHEHINT=Guest

Cheers