Options

Calc and Post VAT Settlement running slowly, only on RTC

afarrafarr Member Posts: 287
The Calc and Post VAT Settlement (report 20) is taking 30 minutes or more to run, even in preview mode (without posting) on RTC.
On the classic NAV client, the report runs in 5 minutes.

The same report used to run in 5 minutes up to last month. The number of records being processed is roughly the same as last month, and hardware has actually been improved.
Other reports are running fine, so it’s strange to see a reduction in performance on this specific report.

Also, just after we restarted the SQL server, the RTC report also ran in 5 minutes; but by the next day it was taking long again.

1. Have you encountered similar behavior, or have suggestions about how to tackle it?

2. This reports locks the GL Entry table, to prevent deadlock with the posting codeunit. However, when running in preview mode, without posting, it doesn’t seem necessary to lock the GL Entry table. Is that correct?


(We had posted in SQL Performance forum last week, but since it’s just one report that is working slowly, and only on RTC, this forum seemed more appropriate)

The environment is as follows:
a) NAV 2009 SP1 W1 RTC, build 32029.
b) The report (and all objects with VAT in their name) are standard NAV without modifications.
c) O.S. Windows server 2008 R2 Standard 64 Bit
d) SQL 2008 R2 Standard
e) 32 GB RAM (SQL limited to use 27 GB)
f) Data Disks RAID 10, Operating system RAID 1.
g) No virtualization
h) Two Processors (recent)
i) Dedicated server hosting only SQL Server and NST middle Tier
j) Size of database is 30 GB
k) SQL optimisation routines are scheduled regularly
Alastair Farrugia

Answers

  • Options
    afarrafarr Member Posts: 287
    The problem occurred on NAV 2009 SP1. Besides, if we restarted the NAV Service Tier, the report only took a couple of minutes. It's only after running the report a second or third time that it took longer and longer.

    Microsoft suggested moving to NAV 2009 R2 (the latest build is 34807), and in fact that solved the problem. In this build, even on the 4th run, the report still took only a couple of minutes.
    Alastair Farrugia
Sign In or Register to comment.