Update 6 for Navision 4.0 SP3: BE CAREFULL

Waldo · 2007-09-21

It is possible.

What I did:

--Create the Config Table
CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)
GRANT SELECT ON [$ndo$dbconfig] TO public

--Disable all index hints
INSERT INTO [$ndo$dbconfig] 
VALUES ('IndexHint=No')

--Enable for only the vendor table, lookup on search name for findfirst, findlast and findset
INSERT INTO [$ndo$dbconfig] 
VALUES ('IndexHint=Yes;Company="DEFAULT COMPANY";Table="Vendor";Key="Search Name";Search Method="-+$";Index=1')

Then, I executed this code in C/SIDE:

//Simple lookup in Customer table
recCustomer.SETCURRENTKEY("Search Name");
recCustomer.SETRANGE(City, 'waldo');
IF recCustomer.FINDSET THEN;

//Same lookup in Vendor table
recVendor.SETCURRENTKEY("Search Name");
recVendor.SETRANGE(City, 'waldo');
IF recVendor.FINDSET THEN;

And the result is as we expect:

SELECT  *,DATALENGTH("BLOB Reference") 
FROM "400SP3_NAV_TEST"."dbo"."Object" 
WITH (READUNCOMMITTED)  
WHERE "Type"=@P1 AND "Company Name"=@P2 AND "ID"=@P3  

SELECT TOP 500 *,DATALENGTH("Picture") 
FROM "400SP3_NAV_TEST"."dbo"."DEFAULT COMPANY$Vendor" 
WITH (READUNCOMMITTED, INDEX("$1"))   
WHERE (("City"=@P1)) ORDER BY "Search Name","No_"

Very simple example, but it explains that it's quite easy to disable the indexhint (if wanted) and add hints one by one.

I'm going to blog this one

colin_leversuch-roberts · 2007-09-24

You need to be very careful using index hints, in most cases using index hints without proper query analysis will most likely lead to degraded performance. I'm troubled that microsoft would add this in as the effectiveness of an index is bound by several factors which would vary from site to site, so your index hint may well not be good for mine, and so on. In 14 years of using SQL Server I think I've only used an index hint a couple of times, and this was in very large joins ( over 8 tables ) where sometimes the optimiser didn't get it totally correct.
If you can, test your queries with and without the hint comparing io, duration and cpu cycles; make sure you test with a typical range of queries and on a dataset which is the same size as your production data.

Sorcerer · 2007-09-24

Waldo wrote:

--Enable for only the vendor table, lookup on search name for findfirst, findlast and findset
INSERT INTO [$ndo$dbconfig]
VALUES ('IndexHint=Yes;Company="DEFAULT COMPANY";Table="Vendor";Key="Search Name";Search Method="-+$";Index=1')

hi waldo,

but for findfirst, findlast and findset the statements are [ , ] and $.
(at least in the table from martinni).
do your code work although?
thanks

Waldo · 2007-09-24

Nice remark. Damn... . I only tested with the FINDSET ($).
So I guess it should be:

INSERT INTO [$ndo$dbconfig] 
VALUES ('IndexHint=Yes;Company="DEFAULT COMPANY";Table="Vendor";Key="Search Name";Search Method="-+[]$";Index=1')

Sorry, folks ... sometimes, I'm a bit too enthousiastic :oops: .

DenSter · 2007-09-25

colin leversuch-roberts wrote:

I'm troubled that microsoft would add this in

Understatement I hope

Exactly why we are suggesting to turn it right off and only implement it if you have any problems.

Waldo · 2007-09-26

They just should disable it by default ... and make it (much more) easier to enable it. May be in C/SIDE.

jfalkebo · 2007-09-26

There are some really good reasons for why we made the change to start index hinting by default. We never make changes like this without thorough performance testing.

The Nav app. was originally written for the Native database which fits this "new" way of writing well performing application code. Even so if we encounter a case where the standard app. does contain code where the ordering doesn't fit the filtering then we are going to handle that as bugs in the app. I would like to counter the suspiccion indicated that index hints can sometimes reduce performance. This is only true if the index hint doesn't fit the statement.

The main reason for making this change is to remove all ambiguity for customers. Unfortunately the world isn't full of Nav MVP's so things have to work out of the box. We have to consider this over the possibility to gain a few percent of performance here and there.

Index hints is a full supported feature of SQL server and there are no plans as of now to discontinue this support.

Moving forward we are going to continue the cooperation with the SQL team and continue the efforts to increase Nav performance on SQL server.

Waldo · 2007-09-26

We never make changes like this without thorough performance testing.

I can (off course) only encourage that. And I also believe that Microsoft can't afford not to do it.

It's just ... in some tuning projects, adding some indexhints turned out to having worse performance then before (I base this on only two customers - I realise this is not very objective). Mainly, because filtering and sorting are two very different things for end users ... .

cnicola · 2007-09-26

Hi Jesper,

I am thrilled to hear from MS (or rather Navision

) people. And even more thrilled to see SQL specific functionality.

While I understand why you would want things to work average well out of the box instead of pandering to us tweak "enthusiasts", in big implementations that kind of pandering would help immensely and also would help Navision's reputation with larger companies.
For instance one suggestion I had that would help us is to change SetCurrentkey slightly. Add a silent parameter (i.e. goes to the default value if not specified) that will control whether an Order By is sent with Settcurrentkey command or not.
Many times I had a table optimized perfectly for filtering and searching records (with the best SQL Indexes I could think of) and I would run into performance problems just because Navision was requesting to have things ordered by the Navision key !!!!!!!!! ](*,)
If it is a silent parameter then all the current Navision code will work just as before but will allow us to go back where we want to and disable it using that parameter.

P.S. And as for your out of the box theory especially with Navision 4.00 where no SQL improvements have been made let's take a key from standard Item Ledger Entry: "Entry Type,Item No.,Variant Code,Source Type,Source No.,Posting Date".
What will SQL do when encountering this? Use it or ignore the hint? (I am thinking of the lack of selectivity of the first field)

jfalkebo · 2007-09-26

We do recognize the filtering/sorting problem in forms but have not reached a conclusion yet. So right now the user has to choose the right index before adding the filter. Maybe the client should not allow filtering on columns that doesn't fit the current sorting (Would be a shame for small tables). Maybe the client should automatically change the sorting to something that fits the filter better. The latter would solve the problem where the user makes a filter on a Name column e.g. "A*" while the sorting is still set to AccountNo. Many users would automatically assume a sorting on the name column anyway instead of below sample result which they get today:

1 - Anders the duck
2 - Al Bundy
3 - Ace Ventura

It's been indicated in this thread that SQL server solves the performance problem when the filter and the sorting are done according to different keys but it really doesn't. Here's a couple of examples where removing index hints doesn't help:
1.A user enters a filter on a column that returns a small amount of the table and sorts on a different column. This first attempt will perform fine without index hints. The user then enters another filter value which returns half of the table. SQL server will use the plan calculated for the first filter value and performance will be bad.
2. A user enters a filter the first time that returns half of the table and while sorting is set to a different column than the filtered column. Whatever access-path SQL server chooses performance will be bad.

So we can't guarantee anything unless the sorting and the filtering corresponds to then same index or the table is small.

jfalkebo · 2007-09-26

As an answer to cnicola's mail

First of all let me say I don't think it's a bad idea with the extra parameter on SetCurrentKey but it has some implications that would be good to explain in this thread.
The reason why we add the order by on many statements where it might seem unnecessary e.g.: "SELECT TOP 1 * FROM myTable ORDER BY A,B,C" is that we support relative scrolling from any position. This is good or bad but we support it. So the app. could call RECORD.NEXT after issuing above statement (Example from FINDFIRST). This is also why we make all the keys uniq. So the parameter you suggest would basically control whether we could make a meaningfull relative move or not.

When SQL server consideres whether to use an index or not it considers the selectivity of all the available columns not only the first so SQL server will be equally happy to use an index whether the most selective column is the first or not.

Finally i hope you will keep tuning all those big installations, now you just have to make more tuning from inside Nav. This is where tools like our ClientMonitor should help.

DenSter · 2007-09-26

jfalkebo wrote:

The Nav app. was originally written for the Native database which fits this "new" way of writing well performing application code. Even so if we encounter a case where the standard app. does contain code where the ordering doesn't fit the filtering then we are going to handle that as bugs in the app.

And that is exactly why I don't like the decision to turn on index hints. Instead of considering SQL Server to contain the bugs and trying to make it behave like NAV, what should have been done is make it easier to implement index hints in the exceptional situations (as in once every few thousand queries) where they could be beneficial. The SQL Server query optimizer is right 99.99% of the time.

By enabling index hints by default, Microsoft has made a considerable effort to make SQL Server behave like the native NAV database server, and that in my opinion is just plain and simply the wrong thing to do. The effort should go into making NAV be more SQL Server friendly, not make SQL Server behave like NAV.

In the SQL Server community it is a well known and accepted fact that you should only use index hinting when you can prove that the query optimizer is making the wrong decision, which happens very rarely.

In my opinion, it would have been much better to create an analysis routine to find the 'wrong indexes', and to have some sort of user friendly form or something that the admin can use to turn index hinting on in those exceptional cases where they are necessary.

DenSter · 2007-09-26

Just had a brain fart

Where we've typically seen bad performance is on large data sets and users that are scrolling around and setting funky filters. What would possible work is when NAV recognizes that the query comes from a UI element (i.e. when the user is on the Item Ledger Entry form, there is a good chance that they're going to put in funky filters, or that they start scrolling around), and it either forces a recompile from the UI or it does an index hint, but only from there. If there would be a way to have some sort of dynamic index hints (that you would be able to turn off if you want to), then put them in from there.

Again, this is just a brainfart, not thought out very well, and I wanted to just put it out there for discussion.

cnicola · 2007-09-27

I don't know Daniel. My initial reaction was negative also.
But the more I think about it the more I like it (I cannot even believe I am saying this, well typing it :P )

First of all we all know how nice and friendly C/Side db was. If we can get the SIFT from Navision, the forgiveness on keys from Navision and the scalability of SQL I think that might not be a bad outcome even if that bastardizes a bit standard SQL. Let's not forget that a generic DB engine (or generic anything) is decent at everything but that not really good at anything.
Also to be fair to MS this is the most elegant and flexible solution.
To anyone that does not like it you just disable it with few SQL lines and you are back to what you got used with (I definitely will for my already tuned systems since I really don't feel like revisting things again).
But all of us that ever did some SQL tuning have encountered a situation where no matter how we designed the key, used the recompile plan guide, kicked the computer, SQL would just not want to use it (especially with 2005 and its love of clustered indexes). So now we have something that helps with that and I think for new dbs I will keep it in mind.

But enough of siding with MS. It really feels unnatural

cnicola · 2007-09-27

jfalkebo wrote:

As an answer to cnicola's mail

First of all let me say I don't think it's a bad idea with the extra parameter on SetCurrentKey but it has some implications that would be good to explain in this thread.
The reason why we add the order by on many statements where it might seem unnecessary e.g.: "SELECT TOP 1 * FROM myTable ORDER BY A,B,C" is that we support relative scrolling from any position. This is good or bad but we support it. So the app. could call RECORD.NEXT after issuing above statement (Example from FINDFIRST). This is also why we make all the keys uniq. So the parameter you suggest would basically control whether we could make a meaningfull relative move or not.

This is where tools like our ClientMonitor should help.

Hi Jesper,

First of all I think whoever uses NEXT after a FINDFIRST should see the system slow down to a dead crawl so they learn their lesson :evil:
But my radical views aside 8) your answer did make me understand more about how things work in Navision. And the reason I suggested a silent parameter was to still allow Navision to work the way you want but have the possibility of make Navision work the way I want.
BTW the reason I want that is legacy C/Side. A lot of times a client will ask to be able to filter on 7 or 8 fields of a table and then see some totals of decimals from that table. C/Side code requires all those fields in the Navision key just to be able to do a Calcsums on the table. I know that out of those 7-8 fields only say 2-3 are selective and need to be in a key (the SQL Index). So in SQL doing the query I get SQL to use my key and all is beautiful and fast. But from Navision due to the sorting request by 8 fields I end up with either some clustered index scan or some weird multiple key hash.

P.S. I used to use Client Monitor a lot. But that really is useful to identify bad programming more than anything else and since my code is always streamlined

no need for that anymore.

Waldo · 2007-09-27

Hm, I don't know, Daniel.
On ledger entry tables, usually, users are not searching that smart (at least not my customers

) ... and usually filtering and sorting does not match, so I definitally don't want to hint indexes.

May be it's possible for the SQL-generator in C/SIDE to check wether the where-clause and the order by clause is a match ... and then forces the hint... .

I don't know ... just a "brainfart" as well

cnicola · 2007-09-27

Waldo wrote:

I don't know ... just a "brainfart" as well

Is it just me or is it starting to smell in here? :sick:

stryk · 2007-09-27

Hi all!

Well, reading this thread several times I have to admit that I don't know where to start to put my two cents - I could write a novel story to post my humble opinion here ...

Just a few things I want to point out (based on many years of struggling with NAV & SQL performance):

Yes, Index Hinting is a standard SQL feature, but this does not implicate that it should be used - because it's a "stupid" feature and only works with very specific queries, where the developer precisely knows what to do.
I doubt that MS has done a thorough performance testing - a test on "Cronus" is never reliable - because performing index hinting - the NAV way - on large tables actually screwes up the system; meaning performance is vanished.
(those of you running a 300GB db with 15Mio Item Ledger Entries: just try :evil: )

Dear Jesper, this should be no offense, but I'm afraid that MS - the part developing NAV in Danmark - has ... let's say: "different knowledge" than all those SQL experts - www.sqlpass.org / www.sql-server-performance.com / www.sqlservercentral.com - I've contacted in the past:

So right now the user has to choose the right index before adding the filter.

With NAV a user can not choose an Index - he can pick a "Key" which actually defines the sorting (ORDER BY).
(and there is a big difference between a Key and an Index - from a db-technological aspect!)
The Index is - and should be - picked by the SQL Server, as it knows which is the best for the current query. Index Hinting is overruling this SQL behavior - here actually the C/AL developer claims to be smarter than the Query Optimizer, which I really doubt.
As discussed in this thread, the NAV way of "hinting" is to define an index based on the sorting - which is stupid. The primary basis to pick the right index is the WHERE clause, thus the filters in NAV.
Finally, the NAV way of "index hinting" is degrading performance remarkably!

I strongly recommend: DISABLE INDEX HINTING:

create table [$ndo$dbconfig] (config varchar(1024))
grant select on [$ndo$dbconfig] to [public]
go
insert into [$ndo$dbconfig] values ('IndexHint=No')

When SQL server consideres whether to use an index or not it considers the selectivity of all the available columns not only the first so SQL server will be equally happy to use an index whether the most selective column is the first or not.

This is not true, or all the SQL trainers (MS!) and experts lied to me (not to mention my personal experiences with numerous SQL systems). With SQL Server it is absolutely crucial to have the most selective fields in the beginning, the least selective at the end. This rule is basically valid for all indexes, but especially for the Clustered Index (I spare the details here).

Anyway, in my opinion NAV still lacks of understanding Index mechanisms of SQL Server. Hope this will improve in near future ...

As mentioned in the beginning, there's much more to say about all this, maybe we'll have a chance to discuss this at EMEA Covergence in CPH ...

Best regards,
Jörg

Waldo · 2007-09-28

stryk wrote:

As mentioned in the beginning, there's much more to say about all this, maybe we'll have a chance to discuss this at EMEA Covergence in CPH ...

Well, let's hope it may come to that. It would be a great opportunity.

Marije_Brummel · 2007-09-28

..maybe we'll have a chance to discuss this at EMEA Covergence in CPH ...

Working on that.

I hope to have an official statement / reply by next monday but I am still making small changes.

jfalkebo · 2007-09-28

We are really happy to see so many enthusiastic participants in this debate. I believe most aspects have been touched upon in this thread and we have done our best to explain the reasoning behind the change. Let me say though that I would have liked more facts for some of the posts rather than general statements. We really need facts to make improvements / changes in the product.

We will arrange for a discussion about this and other Nav/SQL server related issues on convergence.

We are seeing an increasing amount of Nav customers turning to SQL server rather than the Native database so this area is going to get more focus from our side than ever before. It is our hope to expand the knowledge and the community around Nav/SQL as it is already highly needed and will be needed even more in the future.

pdj · 2007-09-30

I guess we can all agree that NAV in most cases will need some optimization to get to handle big databases on SQL no matter what. So the questions are: How do we want NAV to behave out-of-the-box, and which possibilities we have to optimize in C/AL, and which possibilities we have to optimize in the config table.

Most of the discussion in this thread has been about C/AL vs. config optimization, but I would like to discuss how NAV should behave out-of-the-box.

The method used in Update 6 is ok for using the demo database in presentations. It might even be ok in smaller installations as well. However, since it disables the automatic index logic, you also lose one of the advantages over the Native db when the user has to change the key before setting filters.

It also kills several of the solutions I have made the last years for customers, when the solution was made under the assumption that it was executed on SQL. I have gotten used to never use SETCURRENTKEY unless I need a special sorting order. So I have a lot of places where I set filter on i.e. Item No. on Item Ledger without setting the sort order. This code will now be index hinted to the primary key, which of course takes ages no matter how small the database is.

What if the default approach was to force re-compile of all execution plans, and thereby disable the cache that is causing the problems? Remember that we are only talking functionality out-of-the-box. This would still be usable for demonstration, and would even make it possible to demonstrate where SQL is better that Native. And it would be usable for smaller databases as well. (And wouldn't affect existing code)

Wouldn't a forced global re-compile be a better out-of-the-box approach?

Marije_Brummel · 2007-09-30

They have tried recompile on both general and ui level in update 1 if I remember correctly and somehow decided to discontinue.

It is hard to argue with a decision that can do miracles which index hinting can do. On the other hand there is a fantastic query optimizer which can do even bigger miracles correcting users and developers mistakes whether on purpose or not.

Fact is that the problem is in SQL Server, not in Navision. Try searching the web for “Parameter Sniffing” and you’ll find that we are not alone in this subject. So bottom line is that the SQL Server team should take care of it and fix this undocumented feature.

What I have done is what MS should have done in the first place and that is to release a whitepaper describing the problem and how to handle it if is occurs.

There is nothing wrong with the behavior of Navision out of the box.

I have uploaded my whitepaper to MiBuSo but it might take a few days to publish. Send me an email if you want it sooner.

Like Jesper said, we should have a round table meeting about this at convergence. Unlike some years ago there is a lot of knowledge and experience in the fields these days about SQL Performance and how to deal with the issues.

Hynek Muhlbacher and SQL Perform have done a very good job in the past improving the product and it is a shame that this road to improvement is not being used anymore.

For me personally, I have done over 30 projects tuning databases in the BeNeLux since I started as a partner of SQL Perform. I’ve seen a couple of cases of the parameter sniffing problem and will be more than happy to share experience with the Navision development team to tell them how I solved it. Yes, most of the time by using Index Hinting… most of the time.

Please let me say that I do not see myself as the master on SQL. I just do my work in by region and help partners and customers solve issues. I have only seen a couple of examples where this issue happens. Microsoft must have seen hundreds and therefore I am very curious how many of these sites were tested with this update and how they measured the increase of performance.

I would like Microsoft to release their test documentation where they tell us how large the sites were, how many users there were on that system, what the problems were, etc. I also would like to hear how the SQL Server development team participated in this.

Last but certainly not least I would like to thank everyone for their (continuous) participation in this discussion. The fact that a discussion with Microsoft has been started on MiBuSo shows for me the power of the communities!

Waldo · 2007-10-01

Congrats on the white paper, Mark. Nice job!

Allthough the discussion remains, many people (should) know now what the discussion is all about.

=D>

kriki · 2007-10-02

Just a question that comes to me now.
If I have an SETCURRENTKEY(Field1,Field2), so this generates in SQL the statement ... INDEX("$2") ...

What happens if in Navision the MaintainSQLIndex-property of that key is false?

NavStudent · 2007-10-02

Can't they make change to the client to only add the index hint for code, and not when a user filters on form, or searches with the find window?

Mortenr · 2007-10-23

Just wanted to know if anyone have tried to disable index hint on the database after update 6 and experienced table locks, seems to have exprienced quite a few 'The Table_Name table cannot be changed because it is locked by another user. Wait until the user is finished and try again.' lately.. however not sure if it is related to Update 6

NavStudent · 2007-10-23

Hello

I have to update a client system with update 6. The update looks for the .msi file and then it updates the exe.

Do I need to do this on every workstation?

Can I direct it to look at networked .msi file?

Thank you.

colin_leversuch-roberts · 2007-10-23

so I wonder if the navision team really talked to the sql server team? I was at a SQL Server day at Microsoft (UK) and we had some discussion about Navision - apparently the application teams in microsoft are said to have promised to never write index hints - bear in mind my contacts are all "off record" and we were talking sql server rather than navision.
As you know I consider it a very bad idea. I'm still tuning our navision database and have been working with microsoft on a couple of the issues.

kriki · 2007-10-24

NavStudent wrote:

Can't they make change to the client to only add the index hint for code, and not when a user filters on form, or searches with the find window?

Like I wrote somewhere on the forum, something like:

TheTable.RESET;
TheTable.SETCURRENTKEY("Some Index");
TheTable.SETINDEXHITING("Some Other Index");
TheTable.SETRANGE(....);

And also something like this on the forms/...
This would help exactly where it is needed.
And everything remains beautifully in Navision, without having to do something in SQL.

Howdy, Stranger!

Quick Links

Categories

Update 6 for Navision 4.0 SP3: BE CAREFULL

Comments