Howdy, Stranger!
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
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 302 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Extension performance issue
NAVupNorth
Member Posts: 14
Hi guys
We've upgraded a customer to NAV 2018 with v2 extensions. The fields that used to be on the item table are now spread across 5 different extensions using TableExtensions. We're hitting a weird performance issue with sales orders - when a user looks up an item on the sales lines it takes around 4 seconds for the item list to appear. If we uninstall a specific extension it works really quickly though - the item list appears instantly. The TableExtension in this extension has got 1 field in it, a Boolean. As a test we created a brand new extension where this is the only object in it (and it's still only got the 1 field - no code, no dependencies), uninstalled the original version (at which point performance was good) and installed the new one - at which point it immediately slows back to 3 or 4 seconds.
There are no errors and the only thing in the Windows event log is a long running SQL statement which starts with SELECT TOP (@0) ISNULL("Item"."timestamp",@1) AS "timestamp", goes on to list every field on the table including the table extensions, and finishes with WHERE (ISNULL("Item"."No_",@2)>=@24) ORDER BY "No_" ASC OPTION(OPTIMISE FOR UNKNOWN, FAST 50).
There are about 40 bespoke fields in total and we weren't hitting any table definition limits on the old database before the upgrade.
Has anybody seen anything similar?
We've upgraded a customer to NAV 2018 with v2 extensions. The fields that used to be on the item table are now spread across 5 different extensions using TableExtensions. We're hitting a weird performance issue with sales orders - when a user looks up an item on the sales lines it takes around 4 seconds for the item list to appear. If we uninstall a specific extension it works really quickly though - the item list appears instantly. The TableExtension in this extension has got 1 field in it, a Boolean. As a test we created a brand new extension where this is the only object in it (and it's still only got the 1 field - no code, no dependencies), uninstalled the original version (at which point performance was good) and installed the new one - at which point it immediately slows back to 3 or 4 seconds.
There are no errors and the only thing in the Windows event log is a long running SQL statement which starts with SELECT TOP (@0) ISNULL("Item"."timestamp",@1) AS "timestamp", goes on to list every field on the table including the table extensions, and finishes with WHERE (ISNULL("Item"."No_",@2)>=@24) ORDER BY "No_" ASC OPTION(OPTIMISE FOR UNKNOWN, FAST 50).
There are about 40 bespoke fields in total and we weren't hitting any table definition limits on the old database before the upgrade.
Has anybody seen anything similar?
0
Answers
Post the whole query here, or at least everything from FROM clause to the end, and record a record count from every table involved
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sure, the query is in this file https://1drv.ms/t/s!AoVtfXAwsAhAkJJFXIbL4wI4OUj6mA (I couldn't paste the content here, it was being flagged as a security issue!)
The record counts are:
Item (and Item$<guid> table extensions) - 33,789
Value Entry - 5,818,357
Item Substitution - 19
Stockkeeping Unit - 0
BOM Component - 1,019
The Boolean we get the performance issue after adding is called iShop Dummy
Thanks
We are using Business Central On premise & Item Table is having 8 Extension Tables using sql trace we found Join Queries happening for All Tables , Item Page is opening in 8 sec & also Item List istaking same time, we run SQL Index, still the same issue happening ? All other Pages are working fine. Any solution /Help
Thanks