Navision SQL Slow On v5.00 After Upgrade From 3.70

Jonathan2708
Jonathan2708 Member Posts: 552
edited 2008-07-09 in SQL Performance
Hi,

After upgrading a SQL 3.70 database to 5.00 we are finding several custom reports are running considerably slower. Looking at one individual report, the table structure and all indexes are identical in both versions, but the speed is much slower running on the same SQL 2000 server under the version 5 client.

I know there have been changes in the SQL functionality between 3.70 and 5, including the removal of the 'OPTION FAST' parameter. Are there any other SQL changes I should know about in order to resolve the issues. Would the version 5 system be more suited to run on a SQL 2005 server?

Any help appreciated,

Jonathan

Comments

  • Sandeep_Prajapati
    Sandeep_Prajapati Member Posts: 151
    Jonathan2708 wrote:
    Would the version 5 system be more suited to run on a SQL 2005 server?

    YES.
    Sandeep Prajapati
    Technical Consultant, MS Dynamics NAV
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    5SP1 is not supported using SQL2000, otherwise there should not be any difference.

    Is it both on a production server? Same HW/Setup?
    Marije Brummel | Freelance Business Central Specialist
  • Jonathan2708
    Jonathan2708 Member Posts: 552
    Yeah, when comparing the report running on 3.7 / 5.00 they are running on the exact same server (SQL 2000). Only difference is the Navision version.

    Jonathan
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Have you run client monitor? Any query being slow? Are both databases maintained properly? Reindexed/statistics updated etc.
    Marije Brummel | Freelance Business Central Specialist
  • ara3n
    ara3n Member Posts: 9,258
    Hello.

    This is a know bug in 5.0.
    You need to contact MS and ask them for the hotfix.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Jonathan2708
    Jonathan2708 Member Posts: 552
    Hi, thanks for the help guys. Yes we've already run database maintenance plans, update statistics etc. I'm sure it's something different in the way the 2 versions are working with SQL.

    I'm interested to know if there is a hotfix for this - do you know the KB number?

    My next move was to upgrade again from 5.00 to 5SP1, would 5SP1 include the hotfix? I know SP1 is another upgrade for SQL using indexed views.

    Jonathan
  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • ara3n
    ara3n Member Posts: 9,258
    No I don't know the KB number, and yes sp1 does have the fix.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    There is no sense in going to SP1 since that will definately not perform.

    Look at www.waldo.be for the list of fixes.
    Marije Brummel | Freelance Business Central Specialist
  • Waldo
    Waldo Member Posts: 3,412
    I agree with Mark.

    If you look at SP1 and what has been changed .. it's not really for enhancing reading performance, but more for writing performance. You're talking about reports here ... so I guess reading performance is important.

    To start, I would just upgrade to the latest build and test with it ...

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog

Categories