SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1

pdjpdj Member Posts: 643
edited 2008-04-09 in SQL Performance
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?
Regards
Peter

Comments

  • DenSterDenSter Member Posts: 8,307
    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.
  • garakgarak Member Posts: 3,263
    mhm, is this good for sql or not, i think, lets make a test
    Do you make it right, it works too!
  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,307
    What I am interested in comparing is to run adjust cost on a 600GB database with millions and millions of transactions to calculate.
  • jannestigjannestig Member Posts: 1,000
    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
  • DenSterDenSter Member Posts: 8,307
    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.
  • garakgarak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • NobodyNobody Member Posts: 93
    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.
  • DenSterDenSter Member Posts: 8,307
    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.
  • krikikriki Member, Moderator Posts: 9,115
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • davmac1davmac1 Member Posts: 1,283
    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.
  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • NobodyNobody Member Posts: 93
    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.
  • bbrownbbrown Member Posts: 3,268
    But even a simple re-compile (F11) is causing the indexed views to be dropped and recreated.
    There are no bugs - only undocumented features.
Sign In or Register to comment.