[Jason Wilder]

We have come across a bit of a performance issue when sending records to Excel using Ctrl+E on certain pages.  I can replicate this on demand in our test environment as well.  We are on NAV 2016 (executable version 9.0.47444). 

I created a nifty page that shows change log entries for certain pages such as sales orders or formulas.  For example, from a Sales Order you can go to the ribbon and click view change log and it fills a temporary Change Log entry variable with the Sales Header and Sales Line records for the order you are on and opens the Change Log Entry Page with these records.  If you happen to hit Ctrl+E to send the information to Excel our SQL server’s (which is very high end) CPU goes right to 100% and stays there.  Not only that but the process never stops.  If we kill excel and kill the NAV windows client it does not stop the query from running in SQL.  I can see it under “Active Expensive Queries” in Activity Monitor in SSMS.

The only way to get it to go away is to restart the NAV Service Tier.  You can try to kill the processes in sql but that is not so easy since they are shared and there are many processes for this one query.

I have found the root issue which is when you open a Page with a temporary record like this:
PAGE.RUNMODAL(5804,TempItemEntry);        (Codeunit 5801 “Show Applied Entries” OnRun trigger is an out of the box example of this)

For some reason when you hit Ctrl E it seems to be sending the entire table to excel with no filters.  It almost makes sense (but it is bad!) that it is doing this since there are no filters on that page.  Also sending a large table to excel shouldn’t bring our SQL server to it knees either so there must be something else going on with Ctrl+E on top of this.

So this is most certainly a bug but wanted to know if anyone else has run into this on their version of NAV and if maybe it is fixed in a future version?

——————————
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
——————————

[Jonathan Kleist]

An interesting bug you found Jason. I just tried on our NAV 2013 test environment and received the same result where exporting from the Applied Entries resulted in exporting all Item Ledger Entries. I did not get the same results of taxing our SQL server perhaps because we have a small database with only 60,000 entries in the ILE.

I then tried in a Cronus NAV 2017 environment we have running and seems the issue was resolved. Only the Applied Entries exported to excel rather than every record in the Item Ledger Entry table.

——————————
Jonathan Kleist
NAV Administrator
Gustavo Preston
Chelmsford MA
——————————
——————————————-

[Jason Wilder]

Thanks for the responses! 

Jon thanks for testing this out in NAV 2017.  We are planning a technical upgrade in the next month or so which should solve our problem based on your research!

——————————
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
——————————
——————————————-

[Jason Luchka]

?We haven’t necessarily had this issue, but approached this from a different angle. On our Sales Header, we added an action to run a non-NAV report that was written with SSRS. It executes in seconds compared to 10 minutes when we used to let users access the Change Log directly, within NAV. Plus, the report has an option to save to excel (and many other formats) after it renders.

——————————
Jason Luchka
Programmer / Analyst
FFR Merchandising, Inc.
Twinsburg OH
——————————
——————————————-