Is Navision to stupid or is SQL to smart

DenSter

Mark Brummel wrote:

If I have a table in NAV which is clustered Document Type, Document No. Line No. and add an index Document No., Customer No. and look in SQL the index is Document No., Customer No, Document Type, Line No. so SQL seems to be able to find out which fields of the clustered index are already in the secondary index and do not add's it again.

I think it's the NAV exe's that figures this out, not SQL Server. It seems to be NAV's way of enforcing uniqueness of the index.

When you specify a SQLIndex in the NAV table designer, it does not add the PK fields to the index on SQL Server. When you leave out any (or all) of the PK fields, it leaves them out of the index on SQL Server as well. At that time it (it = NAV) knows not all the PK fields are in the index, and consequently sets the secondary index on SQL Server (without all of the PK fields) to NON-UNIQUE.

DenSter

ara3n wrote:

Show all is a bad design?
Are you saying that nobody should do a showall on any ledger tables?
So if they want to research something, they should do what?

I don't think show all is a bad design, it's just that retrieving 2 million records in a client side cursor will take some time, it doesn't matter how well the system is designed. And yes I am saying nobody should ever do a show all on a ledger entry table, because nobody benefits from looking at ALL of them, there is just no use for showing all ledger entries. The first thing you do after you show all is set additional filter criteria, so why even go there?

There are all sorts of things you can do that will perform very badly (scrolling through the item list anyone?). Instead of complaining how bad the design is, or which application is 'at fault', what we should try to think about is ways to make it better.

Maybe one way around this is a special query form, one that starts out empty, because nobody is ever interested in ALL ledger entries, so why wait until that list is retrieved. The user enters some filter criteria and then they click on a button to execute the query.

David_Singleton

DenSter wrote:

...There are all sorts of things you can do that will perform very badly (scrolling through the item list anyone?). Instead of complaining how bad the design is, or which application is 'at fault', what we should try to think about is ways to make it better. ...

A little off topic but... I was thinking back to my first real SQL project on Navision, where we were interfacing Navision to an application running on SQL 6.5. we were mapping data, and we had basically about 70 or 80,000 customers which I had imported earlier from a file they gave me. We were then comparing the mappings, me with those customer in Navision on my Laptop (486 running OS/2 with NAV under Win-OS/2). I was scrolling and searching for customers by Customer number and everything was instantaneous, but things like Ctrl-End equivalents on their system on SQL 6.5 were taking 10-15 minutes. The guy was in awe and could not believe I really had the full customer database.

When he asked how I could do this so fast, I just said "I don't know its how Navision does it, Don't all databases work like that".

The point is we (and our users) are so used to the way Navision does things, that its hard to realize that other systems do things differently. Fr example the Database is really designed to work the way we do, it knows what a Navision form is, and it knows what it needs to do to get that information on the screen. SQL does not know the Navision application, so can't work the same.

Years ago I would have cried to hear someone suggest designing a special request form to work with Item ledger entries, but today with SQL, it is the way to go. We do need to adapt. And not only us. I think we are getting close to the point where Navision need to separate the product into Native and SQL so that they can make the SQL version use SQL properly.

Oh and the company that we were integrating with ... they eventually decided to scrap their C++ SQL product, and became an NSC and rewrote the application in Navision instead.

ara3n

David Singleton wrote:

Years ago I would have cried to hear someone suggest designing a special request form to work with Item ledger entries, but today with SQL, it is the way to go. We do need to adapt. And not only us. I think we are getting close to the point where Navision need to separate the product into Native and SQL so that they can make the SQL version use SQL properly.

I think we are going in that direction 5.1 3 tier is sql only. Native is no longer supported.

Wim_Mulder

David Singleton wrote:

Wim Mulder wrote:

Does anybody heard about the $ndo$dbconfig table to define the tables to get the hint option "RECOMPLE"?
This have helped us a lot at our custumoprs!

Waldo recently posted a very interesting Blog about this here :

Making NAV use a certain index on SQL Server - Waldo's Blog Microsoft Dynamics NAV

Yes I know about that, but there is also an other way of defining the RECOMPLI hint using the $ndo$dbconfig table.

What were added it’s easier to explain in example. Assume that the following string is present in the “$ndo$dbconfig” table:

Example adding the recompile option 1:

INSERT INTO [$ndo$dbconfig] VALUES

('UseRecompileForTable="G/L Account";Company="CRONUS International Ltd.";RecompileMode=1;')

NB: Remember the syntax should use the NAV name for the tables (e.g "G/L Entry") and NOT the SQL naming "G_L Entry". Further every entry have to end with a simi-colon ";". The Default value for the RecompileMode is 1 and leaving out the "Company" will have the effect that the recompile property is set for all Tables names. This means you have the property DataPerCompany set all theses tables will be affected dispite the company.

These mean that for SELECT queries for “CRONUS International Ltd.” company for "G/L Account" table the RECOMPILE hint is used.
Note that “RecompileMode” parameter specifies bit fields which can combine (by logic OR) such values:
• 0 – off;
• 1 – use RECOMPILE hint during table browsing. This is intended for UI activity. It’s a default value if “RecompileMode” parameter doesn’t present.
• 2 – use RECOMPILE hint with AL operations.
If you want to use RECOMPILE in both cases you have to use “RecompileMode=3;”.

We have achived good performance on large Tables like G_L Entry, Cust_ Ledger Entry .....

Does anybody else already have some experiences with this option?

Waldo

David Singleton wrote:

Wim Mulder wrote:

Does anybody heard about the $ndo$dbconfig table to define the tables to get the hint option "RECOMPLE"?
This have helped us a lot at our custumoprs!

Waldo recently posted a very interesting Blog about this here :

Making NAV use a certain index on SQL Server - Waldo's Blog Microsoft Dynamics NAV

well, actually, the $ndo$config has been there for a while for index hinting (where the blog is about). Recently (4.0 SP3 with update) you can use this table for recompile as well ... . More info on: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYLSVQUSPTNTNSMQPYTVNSVLONYQVTYPRPTWKOVPUYTXSWTUVP&wa=wsignin1.0

David_Singleton

Thanks Wim and Waldo, that was very useful information.

garak

Thanks Waldi

Waldo

Waldi?
You know my wife?

garak

Yeah, an blonde with an nice character and beautifull lips ;-)

kriki

colin leversuch-roberts wrote:

Yes ascending numeric keys for the clustered index tend to make for very efficient inserts - effectively a clustered heap. I will be looking at the secondary indexes first, I suspect we will look at clustered indexes in conjunction with table partitioning. I estimate changing from navision indexes to sql indexes will reduce my database size by about 100gb.

I have had big problems with Item Ledger Entry-table when opening the entries for a certain item. When opening the form, SQL2005 used the clustered index, so it read all the table. I also tested this on a small table and it still used the clustered index. I tried all kinds of keys and indexes, but SQL2005 stubbornly used the clustered index.
At a certain moment, I changed the clustered index to "Item No.","Entry No." and SQL2005 takes this index and all the rest seems to work fine too (didn't hear any complaints anymore).
The system is a 64bit with a DB of 80GB. 3.10A objects on a 4.00SP3 client.
6 disk RAID10 15K RPM for the DB and a 4 disk RAID10 15K RPM for the log.
The system and the tempDB on a separate RAID1.

David_Singleton

kriki wrote:

I have had big problems with Item Ledger Entry-table when opening the entries for a certain item. When opening the form, SQL2005 used the clustered index, so it read all the table. I also tested this on a small table and it still used the clustered index. I tried all kinds of keys and indexes, but SQL2005 stubbornly used the clustered index.
At a certain moment, I changed the clustered index to "Item No.","Entry No." and SQL2005 takes this index and all the rest seems to work fine too (didn't hear any complaints anymore).
The system is a 64bit with a DB of 80GB. 3.10A objects on a 4.00SP3 client.
6 disk RAID10 15K RPM for the DB and a 4 disk RAID10 15K RPM for the log.
The system and the tempDB on a separate RAID1.

Did you do bench marks on inserts as well? The beauty of a sequential index (Entry No.) if I understand correctly is that you are inserting at the end of the table. But if you are clustering on Item No. then you are inserting somewhere in the middle, and need to move stuff around.

I can see how it would help on Forms, but how does it affect inserts?

Waldo

David Singleton wrote:

kriki wrote:

I have had big problems with Item Ledger Entry-table when opening the entries for a certain item. When opening the form, SQL2005 used the clustered index, so it read all the table. I also tested this on a small table and it still used the clustered index. I tried all kinds of keys and indexes, but SQL2005 stubbornly used the clustered index.
At a certain moment, I changed the clustered index to "Item No.","Entry No." and SQL2005 takes this index and all the rest seems to work fine too (didn't hear any complaints anymore).
The system is a 64bit with a DB of 80GB. 3.10A objects on a 4.00SP3 client.
6 disk RAID10 15K RPM for the DB and a 4 disk RAID10 15K RPM for the log.
The system and the tempDB on a separate RAID1.

Did you do bench marks on inserts as well? The beauty of a sequential index (Entry No.) if I understand correctly is that you are inserting at the end of the table. But if you are clustering on Item No. then you are inserting somewhere in the middle, and need to move stuff around.

I can see how it would help on Forms, but how does it affect inserts?

This is exactly why you should ask yourself: is my application (or part of my application) for OLTP or OLAP ... . Usually, tuning one is affecting the other.

kriki

David Singleton wrote:

kriki wrote:

I have had big problems with Item Ledger Entry-table when opening the entries for a certain item. When opening the form, SQL2005 used the clustered index, so it read all the table. I also tested this on a small table and it still used the clustered index. I tried all kinds of keys and indexes, but SQL2005 stubbornly used the clustered index.
At a certain moment, I changed the clustered index to "Item No.","Entry No." and SQL2005 takes this index and all the rest seems to work fine too (didn't hear any complaints anymore).
The system is a 64bit with a DB of 80GB. 3.10A objects on a 4.00SP3 client.
6 disk RAID10 15K RPM for the DB and a 4 disk RAID10 15K RPM for the log.
The system and the tempDB on a separate RAID1.

Did you do bench marks on inserts as well? The beauty of a sequential index (Entry No.) if I understand correctly is that you are inserting at the end of the table. But if you are clustering on Item No. then you are inserting somewhere in the middle, and need to move stuff around.

I can see how it would help on Forms, but how does it affect inserts?

I didn't benchmark on it, but I know from some other project in which I did it, that performance remains good.
And don't forget that the T37 with Document No.,Line No,Document Type as clustered index has the same problem.
So to avoid that SQL has to do a split, the indexrebuild can be done with a fillindexfactor that is not 100% so some space remains to avoid splits.

ara3n

at what percent did you set it? 95? 90? I guess you loose some read performance but it's unnoticeable.

DenSter

The correct fillfactor depends on how the table is used, the fragmentation of the index, things like that. The SQL Perform reindexing routine calculates the fill factor based on actual table usage.

kriki

I think I put it at 98%.
I am following also some SQL forums (just reading topics that seem interesting for a Navision DB) and in it I found that the factor can be quit high when there are a lot of records in it. They were talking of about 98%. Considering the large amount of records, 98% gives enough free space to avoid splits.