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
- 304 Dynamics CRM
- 109 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
Data readback delay
njhansen
Member Posts: 37
Basic problem:
When we read data from the SQL Server under NAV, it is occasionally wrong (as in old data that should have been very recently updated).
Architecture:
Web site (IIS 7.5, .Net 4) uses NAV pages exposed as web services to write data to NAV. For performance reasons, it reads most of its data directly from SQL Server, instead of reading pages. The service tier is running on a separate VM from the SQL Server.
SQL Server has snapshot as its default isolation mode.
Just recently, we noticed that a particular web page was responding slowly, and at the same time, noticed that part of the time, when it reads back the data from the SQL Server, it gets the old values instead of the new values it just wrote through the page. (After the page calls the WS to write the data, it reloads and displays the current data, which should be the same as what the user just entered.) Sometimes it gets the correct data immediately, and if you wait a bit and reload the page, it always works.
My first suspicion was, since SQL Server has snapshot isolation enabled, that the data from the WS write was still being copied from tempDB to the main database when the query arrived; however, changing the query to be in serializable (instead of snapshot) mode did not resolve the issue.
I suspect that solving the performance problem with the page / service might hide the issue again, but I'd like to solve whatever the basic problem is.
Presumably, data is either cached somewhere, or not written yet, but I don't know where. Ideas?
When we read data from the SQL Server under NAV, it is occasionally wrong (as in old data that should have been very recently updated).
Architecture:
Web site (IIS 7.5, .Net 4) uses NAV pages exposed as web services to write data to NAV. For performance reasons, it reads most of its data directly from SQL Server, instead of reading pages. The service tier is running on a separate VM from the SQL Server.
SQL Server has snapshot as its default isolation mode.
Just recently, we noticed that a particular web page was responding slowly, and at the same time, noticed that part of the time, when it reads back the data from the SQL Server, it gets the old values instead of the new values it just wrote through the page. (After the page calls the WS to write the data, it reloads and displays the current data, which should be the same as what the user just entered.) Sometimes it gets the correct data immediately, and if you wait a bit and reload the page, it always works.
My first suspicion was, since SQL Server has snapshot isolation enabled, that the data from the WS write was still being copied from tempDB to the main database when the query arrived; however, changing the query to be in serializable (instead of snapshot) mode did not resolve the issue.
I suspect that solving the performance problem with the page / service might hide the issue again, but I'd like to solve whatever the basic problem is.
Presumably, data is either cached somewhere, or not written yet, but I don't know where. Ideas?
0
Answers
do you issue a selectlatestversion before reading back?
with best regards
Jens
We write through the WS version of the page, we read through a direct connection to SQL Server (bypassing the service tier).
It was indeed caching, and it was on the .Net side, not the NAV side, so this is off topic for this forum. An admin can feel free to move or remove it.