Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 312 Dynamics CRM
- 110 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Calc and Post VAT Settlement running slowly, only on RTC
afarr
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
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
0
Answers
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.