Looks fine in X++

"This'll do" in x++

When your code “Looks fine” but forces SQL scans - this can be a secret performance killer if you're not testing for scale or monitoring code execution with the trace parser. This is an easy trap to fall into because the code works for the 1 or 2 tests the developer may execute and the testing is likely with a limited data set further hiding the issue. But what can cause this?

If you put functions around fields in where, SQL often can’t use an index. This can look like this:

// Function on field (often forces scan)

select firstOnly custTable

    where strLwr(custTable.Name) == strLwr(_name);

of this:

// Leading wildcard LIKE (scan)

select firstOnly custTable

    where custTable.Name like '*'+_fragment+'*';

There are several patterns we can use to prevent this but as a general rule, in code execution, we should have a primary key or some kind of identifier that is part of an index that can be used. If we need to identify or get a group of records, use an UI pattern of some kind with a query that allows users to define the parameters and gather specific records then we can process the set of records presented in the Query object. Other fixes include:

• Normalize the input, not the column
• Use prefix searches when possible
• Filter on indexed keys (AccountNum, RecId, surrogate keys), not free text
• If you need “contains” search at scale, use the right feature (e.g., search/indexing patterns) instead of brute-force LIKE

The same applies to date filter - avoid wrapping date fields in conversions/functions in where. Filter directly on the column with ranges. Standardizing a date input to some other format, like UTCDateTime to DateTime, should be performed before the query - not as part of the query. For example, don't do this:

CustTable custTable;

while select custTable
    where subStr(custTable.AccountNum, 1, 3) == "100"
       && strUpr(custTable.Currency) == "USD"
       && custTable.modifiedDateTime.date() < systemDateGet() - 30
{
    info(strFmt("Customer: %1", custTable.AccountNum));
}

This is a much better option:

CustTable custTable;
utcdatetime cutoffDateTime = DateTimeUtil::newDateTime(systemDateGet() - 30, 0);

while select custTable
    where custTable.AccountNum like "100*"
       && custTable.Currency == "USD"
       && custTable.modifiedDateTime < cutoffDateTime
{
    info(strFmt("Customer: %1", custTable.AccountNum));
}