Describe the issue

In the Finnish localization, MatchLines in codeunit 32000000 "Ref. Payment Management" looks up the open invoice for every imported reference payment line:

CustLedgEntry.Reset();
CustLedgEntry.SetFilter("Reference No.", RefPmtImport."Reference No.");
CustLedgEntry.SetFilter("Document Type", '%1', 2);
CustLedgEntry.SetRange(Open, true);
OnMatchLinesOnAfterCustLedgEntrySetFilters(RefPmtImport, CustLedgEntry);
if CustLedgEntry.FindFirst() then begin

(src/Layers/FI/BaseApp/Local/BankMgt/Payment/RefPaymentManagement.Codeunit.al, lines 131-136 on main at e62e7db.)

Field 32000000 "Reference No." on table 21 "Cust. Ledger Entry" is not part of any key in the FI layer (src/Layers/FI/BaseApp/Sales/Receivables/CustLedgerEntry.Table.al). The resulting SQL is

SELECT TOP (1) ... FROM "<Company>$Cust_ Ledger Entry"
WHERE ("Document Type"=@0 AND "Open"=@1 AND "Reference No_"=@2)
ORDER BY "Entry No_" ASC

With no index on "Reference No_", SQL Server scans the table for every payment line, so the import time grows with (number of payment lines) x (size of Cust. Ledger Entry).

Expected behavior

Matching a reference payment to its open invoice should be an index seek, so that importing a reference payment file takes time proportional to the number of payment lines, not to the size of the customer ledger.

Steps to reproduce

1. Use a Finnish (FI) company with a realistic number of customer ledger entries.
2. Import a reference payment file with a few hundred lines using report 32000000 "Import Ref. Payment", which calls "Ref. Payment Management".MatchLines.
3. Run the import with the Performance Profiler on, or look at the long running SQL telemetry.
4. The SELECT TOP (1) on Cust. Ledger Entry filtered on "Reference No." runs once per payment line and dominates the run time.

Additional context

We measured this in a Business Central online sandbox with an extension that runs the identical lookup (same filters, same FindFirst) during a reference payment import of 428 lines:

The whole import went from about 98 s to 3.4 s.

Proposed fix: add a secondary key to table 21 "Cust. Ledger Entry" in the FI layer:

key(Key38; "Reference No.", "Document Type", Open)
{
}

The key fields match the filters in MatchLines exactly, and SQL Server returns the rows in "Entry No." order through the clustered key, so FindFirst needs no sort. "Reference No." is close to unique per document, so the index is selective and cheap to maintain. The key name follows the existing KeyNN naming in the table; any name is fine.

Partners who need this today can add the same key through a table extension, which we have done. Once the key is in the base application, those extension keys become duplicates and can be removed.

I will provide a fix for a bug

• I will provide a fix for a bug