NAV 4.0 SP3 hotfix 6 (build 25143)causes slow SELECT

NaviDeveloper_NL

NAV 4.0 SP3 hotfix 6 (build 25143) causes slow SELECT cursor on big table

We have an issue with a simple cursor on a select query of a big table (700.000 rows) that has a very bad performance on NAV 4.0 SP 3 hotfix 6 (build 25143).
It performs well with NAV 4.0 SP3 without hotfix.
A simple NAV form with two columns of this table is opened and then NAV hangs with hotfix 6 for quite some time. It has nothing to do with index hinting (is off).
Used SQL Server versions 9.0 (1399) and 9.0 SP2 (3042) (no difference in this versions in this problem).

NAV 4.0 SP3

Query when opening the form:
SELECT *,DATALENGTH("Picture") FROM "ACCEPTANCE"."dbo"."Acceptance$Customer" WHERE "No_">=@P1 ORDER BY "No_" OPTION (FAST 33)
@p1=180150153

---

Clustered Index Insert(
……...
|--Compute Scalar(DEFINE:([I4Rank1004]=CWT_ROWID()))
|--Compute Scalar(DEFINE:([Expr1003]=datalength([ACCEPTANCE].[dbo].[Acceptance$Customer].[Picture])))
|--Clustered Index Seek(OBJECT:([ACCEPTANCE].[dbo].[Acceptance$Customer].[Acceptance$Customer$0]), SEEK:([ACCEPTANCE].[dbo].[Acceptance$Customer].[No_] >= [@P1]) ORDERED FORWARD)

Duration 88 msec.

NAV 4.0 SP3 hotfix 6

Query when opening the form:
SELECT *,DATALENGTH("Picture") FROM "ACCEPTANCE"."dbo"."Acceptance$Customer" WHERE "No_">=@P1 ORDER BY "No_"
@p1=180150007

Execution Tree

---

Clustered Index Insert(
...
|--Sequence Project(DEFINE:([I4Rank1004]=i4_row_number))
|--Compute Scalar(DEFINE:([Expr1010]=(1)))
|--Segment
|--Compute Scalar(DEFINE:([Expr1003]=datalength([ACCEPTANCE].[dbo].[Acceptance$Customer].[Picture])))
|--Clustered Index Seek(OBJECT:([ACCEPTANCE].[dbo].[Acceptance$Customer].[Acceptance$Customer$0]), SEEK:([ACCEPTANCE].[dbo].[Acceptance$Customer].[No_] >= [@P1]) ORDERED FORWARD)

Duration: 137349 ms.

How to solve this problem?

garak

mhm, a similar problem i've with 4.03 and SQL2005. After update SQL Server to SQL Server Update 6, all works fine. The Hotfix 6 for 4.03 i also changed, because locking problems.

David_Singleton

You need update 8 I think. Though in some countries update 8 is not released, but is available through support through Partner Source as a hot fix.

David_Singleton

Oh and this is the place to get the best information:

Platform updates overview - 3.70.B - 5.0 SP1 (updated again) - Waldo's Blog Microsoft Dynamics NAV

NaviDeveloper_NL

KB950920 / hotfix 8 indeed solves the problem.