Slow performance on NAV2015 List Page

Business Central/NAV Developers Forum
1

Hello together,

I’m facing a quite weird issue.

There is a table with 15 flowfields defined and 176 records.
When I run the list page for this table, it takes around 10 minutes to open.

OK, one could say it is a key issue, flowfields pointing to tables with insufficient key definition and SumIndexFields, but…

When I redesign the page in the following way:

OnFindRecord(Which : Text) : Boolean
POSJournal.COPYFILTERS(Rec);
POSJournal := Rec;
FoundRec := POSJournal.FIND(Which);
Rec := POSJournal;
EXIT(FoundRec);

OnNextRecord(Steps : Integer) : Integer
POSJournal.COPYFILTERS(Rec);
POSJournal := Rec;
RealSteps := POSJournal.NEXT(Steps);
Rec := POSJournal;
EXIT(RealSteps);

OnAfterGetRecord()
CALCFIELDS(Cash,"Debit Card","Credit Card",Check,Others,Voucher,"On Account","Total Payments");
CALCFIELDS("Sale incl. VAT","Sale excl. VAT","Credit Memo incl. VAT","Credit Memo excl. VAT","Special Documents","Payment Discount");
CALCFIELDS("Purchase Invoice incl. VAT","Purchase Invoice excl. VAT","Total incl. VAT","Total excl. VAT")

So actually looping the records myself and not let NAV generate weird SQL statements, it opens in less than two seconds.
(The first CALCFIELDS row is targeting one tabel, the other two rows are targeting another table)

Has anybody else faced this issue?

2

i assume to many flowfields, keys, sumindexfields.

better reduce the no. of flowfields and find an alternative solution, e.g. fill a temp table with the needed values and display that in the page.

3

Thanks Jonathan,

But how would you explain the fact that it is working when I run the page with my code and fetch the records myself?
The SQL Profiler also does not bring us any further as we just see ONE huge statement with an enormous duration.

4

Stuff like this makes me curious! [:^)]

My approach to this would be “step-by-step”:
Remove all flow-fields from the page.
Then add flow-fields again, one-two-three at the time, run the page and locate when the system starts to slow down.
And all this done letting NAV perform the [auto]calculation.

5

Haha, I did that, actually.

NO flowfields where displayed at the page at all - but that does not make any difference in the SQL statement or in the performance.

Same thing works with NAV2013R2 (same tables and pages) with a similar amount of records!!!

6

Select “Disable SmartSQL” on your NAV Service Tier and restart. This will break the SQL calls out to individual statements possibly allowing you to find the problems easier.

7

Thx, I will give it a try.