Navision SQL Slow On v5.00 After Upgrade From 3.70

Jonathan2708

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

Sandeep_Prajapati

Jonathan2708 wrote:

Would the version 5 system be more suited to run on a SQL 2005 server?

YES.

Marije_Brummel

5SP1 is not supported using SQL2000, otherwise there should not be any difference.

Is it both on a production server? Same HW/Setup?

Jonathan2708

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

Have you run client monitor? Any query being slow? Are both databases maintained properly? Reindexed/statistics updated etc.

ara3n

Hello.

This is a know bug in 5.0.
You need to contact MS and ask them for the hotfix.

Jonathan2708

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

[Topic moved from Navision forum to SQL Performance forum]

ara3n

No I don't know the KB number, and yes sp1 does have the fix.

Marije_Brummel

There is no sense in going to SP1 since that will definately not perform.

Look at www.waldo.be for the list of fixes.

Waldo

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 ...