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

NaviDeveloper_NL
Member Posts: 42
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?
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?
0
Answers
-
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.Do you make it right, it works too!0
-
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 Singleton0
-
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 NAVDavid Singleton0 -
KB950920 / hotfix 8 indeed solves the problem.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions