Slow Lot No. Selection

fufikk

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:

• 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

Kowa

The reservation status is not part of the key and the other ones are probably not very selective (and in the middle).
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.

fufikk

I think that the main issue is that I have "prospect" entries left in the Reservation Entry table and to my knowledge, that should not be the case.

fufikk

Ok, problem had been tracked down... There was a modification made that assigned Lot Nos to production order component lines. It had a "feature" of creating a reservation for Lot with 0 available quantity. When production order changed status to finished, those tracking lines got left as reservation entries with no source or quantity information.