SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1

pdj

I would just like you to take a look at this new behavior of NAV5SP1:
http://blogs.msdn.com/microsoft_dynamic ... n-sp1.aspx

It seems to be a good solution, isn't it?

DenSter

I don't know if it is good or not. I did notice today that there are a few new threads that SP1 is performing very slowly, almost as if indexhinting is on. Maybe that's a coincidence though.

garak

mhm, is this good for sql or not, i think, lets make a test

bbrown

I ran the following test using 2 copies of the same NAV 5.0 database. One was run using the 5.0 client. The other was run using the 5.0 SP1 client.

Preparation:

Created a new table ("Entry No.”, “Posting Date”, Quantity, Amount)
Primary Key is "Entry No."
Secondary Key "Posting Date" with SumIndex values on Quantity and Amount

TEST 1 (inserting records)

This would test the improvements of both indexed views and bulk insert

This test inserted 10 records for each day over a 10 year range (01/01/98 - 12/31/07)

This was a total of 36,520 records inserted

The 5.0 client took 18,438 milliseconds to complete the task

The SP1 client took 7,735 milliseconds to complete this task

Performance improvement 58%

TEST 2 (Update)

This would test the improvements of both indexed views (modify is not buffered

This test would update the records from test 1 by adding 2 to each quantity and amount

This was a total of 36,520 records updated

The 5.0 client took 277,860milliseconds to complete the task

The SP1 client took 191,228 milliseconds to complete this task

Performance improvement 31%

Granted this is a very simple test but I think it shows some promising results.

DenSter

What I am interested in comparing is to run adjust cost on a 600GB database with millions and millions of transactions to calculate.

jannestig

HI Denster

We have such a database using nav in a huge environment based off 4.03 and are looking to do a technical upgrade to see what performance gains will be its over 500gb and we still have 5 more regions to join the DB yet , We are getting direct MS support with this and hoping to get performance boosts on the batch cycle run now, if you look at the SQL performance webcast on partner source by hynek muhlbacker you will see us listed as one of those pushing the limits of SQL for NAV

Since i am overseeing the whole think i can let you know what the results were

DenSter

Excellent Jan, I would be very interested in your experiences. I'm involved in a few very big implementations, but I'm reluctant to give my customer the advice to move to SP1 until I know that the indexed views can handle totalling millions of records and still perform well.

garak

super jan, let us know your experiences.

in my little tests (SQL2005, 32bit, 167GB DB, 16GB RAM) the SP1 are faster then the 5.00

Nobody

Just a personal expirience but the indexes in the new views that replace SIFT often need to be tuned to achieve optimal performance. No matter what they do with the product the default indexes will always need to be tuned on any customer of any real size.

DenSter

Yeah you'd think so, but make no assumptions. There seem to be some issues with defining custom SQLIndexes in SP1, and I don't know for instance whether modifying NAV keys or disabling MaintainSQLIndex will have any impact on the indexed views.

kriki

DenSter wrote:

Yeah you'd think so, but make no assumptions. There seem to be some issues with defining custom SQLIndexes in SP1, and I don't know for instance whether modifying NAV keys or disabling MaintainSQLIndex will have any impact on the indexed views.

The indexed view follows the patern of the NAV key and not it's SQL counterpart (SQLIndex-property).
MaintainSQLIndex=No doesn't have any impact on the view. You need the MaintainSIFTIndex to create/delete the indexed view.

bbrown

One issue we have run into is that Navision is dropping and recreating the indexed views anytime the table is compiled. This can take some time on tables with a lot of data. This has been reported to Navision development and we are waiting a response.

It seems they took the simple approach and a dropping the views even if it is not needed.

davmac1

I believe that is a SQL Server requirement for managed views. Anytime there is a change to any of the underling tables, you have to drop the view, make the table change and the recreate the view (databinding).

Now if the table change does not affect the SQL table (code changes for example), then there is no need to drop the view.

bbrown

You only need to drop the indexed view if something that is referenced by the view changes. Things like adding a new field do not require the view to be dropped.

Nobody

Correct, all the SIFT views or atleast the ones I looked at are built WITH SCHEMABINDING which means the view only needs to be dropped if the schema change to the table effects the defintion of the view. Not sure it really matters because if you add a field to a table in NAV the client autmatically drops and recreates the SIFT view associated with when you compile regardless of whether the schema changed affected the view or not.

bbrown

But even a simple re-compile (F11) is causing the indexed views to be dropped and recreated.