dynamicsusers: Is Navision to stupid or is SQL to smart

[Blog bot]

Currenly I am again fighting with SQL2005 at a customer.

Many, many reads on tables whilst there are perfect and selective indexes.

Now I've never experienced this problem on SQL2000. SQL always uses the right index for the query.

I also know Navision has not changed the communication with SQL.

What has happend to SQL2005?? What have they changed that causes this major bug!

There are plenty of workarounds like index hinting and adding the recomile option, but surely this is not a solution. It takes like forever to find the bad queries and find a way to solve them. You do not want to create a indexhint for every query, that would mean that you have Native behaviour again.

The problem seems to be (IMHO) in the clustered index. SQL2005 is much more keen on that than SQL2000. Every SQL expert loves to spend hours and hours to think about the proper clustered index for a table.

If you show the indexplan of Navision to a SQL expert they will laugh. Every entry table is custered by the entry no whilst in the SQL world it is normal to cluster on the most common selective filtered field.

So what is this field in Navision. Let's have an example.

Take the customer ledger entries for example. They could be clustered by Customer No. and Posting Date. But what about filtering on Document No.? Or External Document No.? What about Transaction No. or the Open boolean.

Maybe we should redesign this table for SQL. If Customer No. is the proper clustered index and Document No. is used by Navigate, then maybe we need Navigate Entries? This can be a small join table which is very common in relational databases.

Then we have the Open field. What a nightmare. What if we add a new table to Navision. Just for open entries? This is a small and compact table with fewer entries than your historical entry table.

Last but not least we have the apply to field. This can be very easily solved using a temporary table in a singleinstance codeunit. When a entry is selected you just copy that entry to the codeunit and you read this when applying the entries.

Will redesigning NAV solve the SQL2005 issues? Maybe, maybe not. Fact is that every day maybe hundreds of installations suffer from this issue and it starts to become a real problem for partners since "solving" costs a lot of time and is often not billable.

Any thoughts or comments are more than welcome.



Подробнее... http://dynamicsuser.net/blogs/mark_brummel...l-to-smart.aspx