Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K 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
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 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
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Slow Lot No. Selection
fufikk
Member Posts: 104
Hi All!
A customer is using lot numbers on some of the items. Those items are often used as components on Prod. Orders. When they open Item Tracking Lines form and click the assist button on the Lot No., it can take anything from 30 sec to even couple of minutes before the form opens up.
So far I managed to establish this:
I tried to find out where exactly or which piece of code in NAV takes the longest time to execute and found out that it's AdjustForDoubleEntries function in C6501 - Item Tracking Data Collection.
When you look into that code, you will see that first there is a check that decides if the function should do its thing or not:
The Reservation Entry table holds a few thousands of records that meet that criteria, but all of them have quantity fields = 0, no source information or anything that could point where they came from.
The idea I'm thinking about is to filter out all 0-quantity records, but I'm not really sure what other effects that might have.
If you have anything that could push me in any direction to resolve this, please do share
A customer is using lot numbers on some of the items. Those items are often used as components on Prod. Orders. When they open Item Tracking Lines form and click the assist button on the Lot No., it can take anything from 30 sec to even couple of minutes before the form opens up.
So far I managed to establish this:
- problem appears even if I'm the only user who's logged it
- it's not an SQL issue - SQL Profiler stops showing new entries long before the list of lot numbers opens
I tried to find out where exactly or which piece of code in NAV takes the longest time to execute and found out that it's AdjustForDoubleEntries function in C6501 - Item Tracking Data Collection.
When you look into that code, you will see that first there is a check that decides if the function should do its thing or not:
// Check if there is any need to investigate:
TempGlobalReservEntry.RESET;
TempGlobalReservEntry.SETCURRENTKEY("Source ID","Source Ref. No.","Source Type","Source Subtype","Source Batch Name");
TempGlobalReservEntry.SETRANGE("Reservation Status",TempGlobalReservEntry."Reservation Status"::Prospect);
TempGlobalReservEntry.SETRANGE("Source Type",DATABASE::"Item Journal Line");
TempGlobalReservEntry.SETRANGE("Source Subtype",5,6); // Consumption, Output
IF TempGlobalReservEntry.ISEMPTY THEN // No journal lines with consumption or output exist
EXIT;
The Reservation Entry table holds a few thousands of records that meet that criteria, but all of them have quantity fields = 0, no source information or anything that could point where they came from.
The idea I'm thinking about is to filter out all 0-quantity records, but I'm not really sure what other effects that might have.
If you have anything that could push me in any direction to resolve this, please do share
0
Answers
If you add a new key for these 3 fields, the most selective one (that presumably removes the most unwanted records) at the beginning and use that key solely for the ISEMPTY check first you should get a faster response.
The code block below this one resets and reselects the above key anyway to do the further processing in that function (for all the cases where the dataset was NOT ISEMPTY) so there are no negative side-effects to be expected.