Options

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

NaviDeveloper_NLNaviDeveloper_NL Member Posts: 42
edited 2008-05-20 in SQL Performance
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?

Answers

Sign In or Register to comment.