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.
The SourceTableTemporary is a new property introduced in 5.0 giving all kinds of new capabilities of showing data that is not even in the database.
Instead of showing real data from the database, the form is running on an empty table when starting up, so you will have to populate it with data.
Step 1.
Create a normal form with the wizard and define your sourcetable. Select the columns you want to (ab)use.
Make sure to select the SourceTableTemporary property.
In our example we will use table 18, Customer and select columns No. and Name.
Step 2.
Create a new function "InitTempTable"
In this function define a Local variable "Cust", type Record, Subtype 18. (Customer).
Write the following piece of C/AL Code
| Code: |
Cust.SETFILTER(Balance, '>1000');
Cust.SETFILTER("Country/Region Code" , 'NL');
If Cust.FINDSET THEN REPEAT
Rec := Cust;
INSERT;
UNTIL Cust.NEXT = 0;
Cust.SETFILTER(Balance, '>500');
Cust.SETFILTER("Country/Region Code" , '<>NL');
If Cust.FINDSET THEN REPEAT
Rec := Cust;
INSERT;
UNTIL Cust.NEXT = 0; |
Step 3
Go to the OnOpenForm trigger and put the new function in there.
Result
A view of your customers with a filter that is normaly not possible with the normal filters still available.
The idea and example are from form 634 Chart of Accounts Overview in 5.0. You'll also find a great example there of how to expand and collapse.
For a customer I've built a view combining 2 tables based on a date filter with quantities summed up from other tables. It is very easy to use and setup and when using proper indexing the performance is perfect.
Good luck.