slow navision on sql

kriki

-5.0 SP1 R2: R2 doesn't exist. It is 50SP1 with its different hotfixex
-2009 SP1 R2: or 2009SP1 or 2009R2.

-SQL Views instead of Cursors:
You can't choose between using views or cursors in NAV. NAV uses both.

About SQL:
-avoid cursors if possible
-views: a view is nothing more than a predefined select-statement. At the moment the view is run, the underlying select is run.
-indexed view: a view can also have an (or more) index(es) defined on it. This means that when the view is run, it gets it data from its index which is faster. On the other hand, SQL needs to maintain an extra index. So you need to see if maintaining the index is worth the cost.

RedFoxUA

kriki wrote:

-5.0 SP1 R2: R2 doesn't exist. It is 50SP1 with its different hotfixex

I can read what it is NOT HotFix only!
March 2, 2010: Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1 has been released.

-2009 SP1 R2: or 2009SP1 or 2009R2.

OK, my fault

-SQL Views instead of Cursors: You can't choose between using views or cursors in NAV. NAV uses both.

I have asked about this comparison or selection?

About SQL:
-avoid cursors if possible

How you propose to perform it? from C/AL-code?

-views: a view is nothing more than a predefined select-statement. At the moment the view is run, the underlying select is run.

Are you sure, what this applicable for 2009R2 or Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1? Is view only stored Quest?

-indexed view: a view can also have an (or more) index(es) defined on it. This means that when the view is run, it gets it data from its index which is faster. On the other hand, SQL needs to maintain an extra index. So you need to see if maintaining the index is worth the cost.

Again, are you sure, what this applicable for 2009R2 or Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1?

To be more clear, I want to re-write my first question:
How about your advice for NAV 2009 R2 release (Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1)? I know what we can use SQL View for Key instead of extra-table (and we have "delayed insert").

David_Singleton

What is your question? Are you having performance issue? i so what is the specific problem you are trying to solve?

stryk

RedFoxUA wrote:

Again, are you sure, what this applicable for 2009R2 or Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1?
To be more clear, I want to re-write my first question:
How about your advice for NAV 2009 R2 release (Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1)? I know what we can use SQL View for Key instead of extra-table (and we have "delayed insert").

As David hinted, a simple question would probably give you a simple answer (more or less). Without knowing your problem we cannot give any "advice".

Again:
All recent NAV version from 4.0 SP3 U6+ (and later) are using "Dynamic Cursors".
All NAV versions since 5.0 SP1 (and later) are using VSIFT (= Indexed Views) instead of SIFT (= SumIndex tables)

There's no choice, this is a matter of the NAV client version/build.

David_Singleton

stryk wrote:

All NAV versions since 5.0 SP1 (and later) are using VSIFT (= Indexed Views) instead of SIFT (= SumIndex tables)

There's no choice, this is a matter of the NAV client version/build.

5.00 also uses VSIFT now, has for some time now. Found that out the hard way when a client needed to apply a hot fi to 5.00 to fix some issues and after applying found that SIFT had gone.

VSIFT works pretty good of course, so its not a problem, but these things need to be better documented, and each time a change like this is made is should be clear.

RedFoxUA

David Singleton wrote:

What is your question? Are you having performance issue? i so what is the specific problem you are trying to solve?

Yes, I have performanc issue - I want to speet up posting.
And I want what NAV will generate SQL-requests without "WITH UPDATELOCK" in the standard transactions.

David_Singleton

RedFoxUA wrote:

David Singleton wrote:

What is your question? Are you having performance issue? i so what is the specific problem you are trying to solve?

Yes, I have performanc issue - I want to speet up posting.
And I want what NAV will generate SQL-requests without "WITH UPDATELOCK" in the standard transactions.

OK, so you have TWO questions.

So the first one you want to speed up posting. First check that your hardware is good, then the best place to start is the code. Figure out where you can optimize it to make it faster. Look at the processes and see if things are being done that you don't need.

Second question the removal of WITH UPDATELOCK, you will need to contact Microsoft and ask them to do that for you.

My suggestion is to forget your second question and focus more on the posting speed issue.

RedFoxUA

David Singleton wrote:

OK, so you have TWO questions.

So the first one you want to speed up posting. First check that your hardware is good,

Done approx 1 year ago \:D/

then the best place to start is the code. Figure out where you can optimize it to make it faster. Look at the processes and see if things are being done that you don't need.

Done several months ago..

Second question the removal of WITH UPDATELOCK, you will need to contact Microsoft and ask them to do that for you.

FYI - I were consulted MS-specialists in some business-functionality, but MS SQL-integration was not my part. I had discussions with some of them and proposition was not really applicable. => I have posted question here.

My suggestion is to forget your second question and focus more on the posting speed issue.

I can't do it, because they are "linked points.."

P.S. Excuse me, but I need exact answers on my points! I know how R2 to use View instead of table, but I need to know how to setup (if it is possible now) SQL to avoid WITH UPDATELOCK for standard posting procedure (CU 80, ..22, ..12, ..), beucase I have more then 50 simultaneously working users.

stryk

RedFoxUA wrote:

Yes, I have performanc issue - I want to speet up posting.
And I want what NAV will generate SQL-requests without "WITH UPDATELOCK" in the standard transactions.

Degraded performance could have a gazillion reasons, thus there are plenty of potential solutions. There is no easy answer, it requires thorough investigation of the problem. This is a permanently discussed issue and I recommend to search MIBUSO about "sql performance" and you'll find lots of advices! Additionally I highly recommend to read NAV/SQL Performance related BLOGS

As David said: forget about the UPDLOCK - the only way to get rid of it will mean to jeopardize data-integrity. No way changing that in posting routines.

Hence, to speed up the postings (or any other process) you need to ...
... have a solid platform, sufficient hardware and optimal configuration. You said your HW was checkcked one year ago, well that doesn't mean it fulfills the requirements of today!
... optimize your SIFT/VSIFT-, table- and index- structures. Again: thorough investigation is mandatory!
... optimize the C/AL code!
... optimize NAV setup (e.g. having few "Dimensions", having no "Analysis Views" updated on posting, etc.)
... optimize your workflow (e.g. to avoid conflicting business processes)

RedFoxUA

stryk wrote:

... have a solid platform, sufficient hardware and optimal configuration. You said your HW was checkcked one year ago, well that doesn't mean it fulfills the requirements of today!
... optimize your SIFT/VSIFT-, table- and index- structures. Again: thorough investigation is mandatory!
... optimize the C/AL code!
... optimize NAV setup (e.g. having few "Dimensions", having no "Analysis Views" updated on posting, etc.)
... optimize your workflow (e.g. to avoid conflicting business processes)

Thanks a lot!

rdebath

RedFoxUA,

Your second problem doesn't depend on the version of Navision you are running because it's rooted in the ancient history of Navision. The "Native" database, Navision's normal database back in the last century, doesn't have record locking, it only locks at the whole table level. In the early years of this century an MS-SQL backend was added this was quite a bit slower than the standard database but had the serious advantage that the engine would usually ignore the SETCURRENTKEY index and reliably pull records from the database using the index that would scan the fewest records.

Now MS-SQL does do record locking and Navision A/S tried to take advantage of this automatically by not doing and exact simulation of the Native DB (ie it doesn't use TABLOCK). Unfortunately the Application programmers (C/SIDE developers) didn't take this on board and the standard response to a deadlock message is to increase the amount of locking; to the extent that the normal posting flow is now to use the "giant lock" pattern. That is, every posting starts with:

GLEntry.LOCKTABLE;
GLEntry.FINDLAST;

So your mission, should you choose to accept it, would be to remove those two lines where ever they occur and rewrite, or perhaps just modify, the following C/SIDE code to not lock anything shared until the very end. This is not a small job.

But there is some 'good' news. You see since SQL 2005, ms-sql has become very bad at choosing the lowest cost index so it's likely that there are many places where you can hand optimise the queries to force the so-called query optimiser to actually do it's job. In addition the fact that the users are saying things are gradually getting worse implies there are some easy wins where the optimiser is choosing a table scan over any indexes that may be available; with luck simply adding some new indexes (and don't forget to clean up unused ones) or adding a SETCURRENTKEY will get them.

PS: With the downgrades to the SQL generated by NAV this has become useful for SQL databases too.

stryk

rdebath wrote:

So your mission, should you choose to accept it, would be to remove those two lines where ever they occur and rewrite, or perhaps just modify, the following C/SIDE code to not lock anything shared until the very end. This is not a small job.

Just removing could be dangerous! I suggest to check out this one: http://dynamicsuser.net/blogs/stryk/archive/2010/10/22/solving-blocking-issues-practical-examples.aspx (Example #2)

rdebath

No, it's worse than that, the design pattern isn't just for preventing deadlock errors involving the G/L, those two lines are a mutex that allows only one thread past that point. This mutex will prevent issues with things like incrementing unlocked records anywhere in the process after that point in any called codeunit.

So your example two is NOT 'safe' if there's a bug in the "warehousing" or the "resources" or some other thing that's called during posting a shipment because the "GL-mutex" means that reading before locking is no longer a true bug and will not cause a problem, without your change.

Nevertheless, you are right in part, the fact that those two lines are within a "RECORDLEVELLOCKING" condition means the code has been tested without the mutex. As long as the Native DB exists that is.

kriki

Having good hardware is ok. But is it configured correctly? (eg. no RAID5)
Do you have maintenance plans for indexrebuilding in place?
Are your clients running on PC's that are ok? The speed of posting of an invoice depends also on the speed of the PC that does the posting.
How many indexes do you have on our tables involved in the posting?

BTW: removing these lines:
GLEntry.LOCKTABLE;
GLEntry.FINDLAST;

will not help anything. They are needed because NAV needs to find the last "Entry No." to create the new records. By removing those, you will probably make things worse because if 2 users are posting at the same time, you will get inserts with "Entry No." that already exists.
For the moment, posting in NAV is NOT parallel. Hopefully Microsoft will change that in the future. And now they can because the native DB does not exist any more.

About SQL:
-avoid cursors if possible

How you propose to perform it? from C/AL-code?

This is in case you want to do something directly in TSQL and not from C/AL

-views: a view is nothing more than a predefined select-statement. At the moment the view is run, the underlying select is run.

Are you sure, what this applicable for 2009R2 or Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1? Is view only stored Quest?

Again : TSQL. A view (actually indexed view=view with an index on it) are only used for the SIFT-fields in NAV starting with NAV50SP1.

-indexed view: a view can also have an (or more) index(es) defined on it. This means that when the view is run, it gets it data from its index which is faster. On the other hand, SQL needs to maintain an extra index. So you need to see if maintaining the index is worth the cost.

Again, are you sure, what this applicable for 2009R2 or Platform Update 2 for Microsoft Dynamics NAV 5.0 SP1?

Again : TSQL. A view (actually indexed view=view with an index on it) are only used for the SIFT-fields in NAV starting with NAV50SP1.