SLOW OR FAST? PERFORMANCE CONSIDERATIONS FOR THE WHILE SELECT STATEMENT

on

 


During my career, I have had to deal many times with performance issues. There are many different causes for this. It can be related to hardware, software, configuration and more. In this post, I will explain two scenarios where a while select statement caused performance issues and how to mitigate them.

While select statement

With the while select statement in x++, you can interact with data in the SQL database. Data is being retrieved in a table buffer. The broader the select statement, the larger the table buffer will be and will need load and transfer times. Using supported clauses, you can limit the result set; both the number of records and number of columns. When you are using the where clause, it will limit the number of records, but can actually increase the loading time. This depends on the complexity of the select statement and if your search is supported with correct indexes. Typically, when doing performance investigations, we are used to having a look at missing indexes. This is not always the root cause for performance issues.

The word ‘while’ in while select indicates a loop. It will loop all records from the table buffer. If you have x++ statements as part of the loop which are time-consuming, then this needs a closer look. In some cases, a developer might have made another mistake that could lead to performance issues or is not aware of other statements which might be faster in execution times. I will now continue with my examples.

Selecting all records unintended

When data needs to be read or updated for e.g. a single customer, we usually take the next statement:

while select forUpdate myTable where myTable.CustAccount == _custAccount
{
    // do something...
}

This select statement looks innocent, but it isn’t. Assume that the indexing was defined properly. The text ‘_custAccount’ indicates it is provided with a parameter in the current method. If the value is e.g. ‘US-101’ it will loop the records correctly. The number of records will determine how much time is consumes by the ‘do something’ part.

In several cases, I had a statement like this which caused huge performance issues, found with SQL header block information, Trace Parser analysis and/or debugging. Actually it was caused by missing data; the where clause variable was empty. In the example above, the _custAccount variable was empty. In that case, it is returning all records.

Mitigation

It would be recommended to first evaluate if the variable has a certain value before actually using it in the while select statement.

Compare it with a division by zero which should be prevented. The example above can be easily adjusted to prevent the performance issue like this:

if (_custAccount)
{
    while select forUpdate myTable where myTable.CustAccount == _custAccount
    {
        // do something...
    }
}

Or when this is the only statement in your method, you can also use:

if (_custAccount == '')
{
    return;
}

while select forUpdate myTable where myTable.CustAccount == _custAccount
{
    // do something...
}

From my experience, in two cases, there were multiple nested while select loops where in between certain fields of one or more records did not have a value. The outcome of a field in the first while select iteration was used for the where clause. This caused looping through all records of 4 different tables. When it was read only, then only the current user was affected. In update scenarios, like provided in the example code, it will actually cause a database lock which could have a duration of several minutes. In your imagination, think of having this as part of a sales invoice update and during end month process when multiple legal entities needs to complete invoicing it will be a complete business stand still due to database locks.

Prevent nested loops

During the build, the best practices will warn you for nested loops. You can bypass the warnings by having the while select of the second level in a separate method, but you can also think of another approach to improve performance.

Suppose, we have multiple tables with data that needs to be copied, for example as new version or from worksheet tables to transaction tables. It is about a Header, Line and Details. Let’s draw an easy example:

Header

IDDescriptionRecId
AAADescription AAA101
BBBDescription BBB102
CCCDescription CCC103

Line

HeaderRefRecIdGroupRecId
101Accountant201
101Sales clerk202
101Sales manager203
102Controller204

Detail

LineRefRecIdUserRecId
201User 1301
201User 2302
202User 3303
202User 1304
203User 4305
203User 5306
204User 4307

Suppose we need to copy the Lines with Details from Header AAA to a new Header: CCC. In the first table, the record for CCC is already created. It is quite normal to think that we need to loop the old data and per line copy the Details. The coding could look like this:

while select lineSource where lineSource.HeaderRefRecId == _headerRecIdSource
{
    line.initValue();
    line.HeaderRefRecId = _headerRecIdNew;
    line.Group          = lineSource.Group;
    line.insert();

    while select detailSource where detailSource.lineRefRecId == lineSource.RecId
    {
        detail.initValue();
        detail.LineRefRecId = line.RecId;
        detail.User         = detailSource.User;
        detail.insert();
        
    }
}

This code example will work and be a viable solution if there are not much records expected in the Line and Detail tables. The more details and line, the less performant this option will be. When having a large number of records, your aim should be to limit the execution time for the copy process. The statements insert_recordset, update_recordset and delete_from are handling multiple records at the same time and are much faster as there will be only one SQL call involved. As the execution times are quicker using these statements, it is not always the best to use them. I would recommend reading the blog from Denis Trunin about Blocking in D365FO for more understanding.

Now how to use this for the example above? The trick is to add a dummy field for the record ID of the Line table. This will initially have no value (0 for integer64).

Line

HeaderRefRecIdGroupTemporaryRecIdRecId
101Accountant0201
101Sales clerk0202
101Sales manager0203
102Controller0204

With help of this field, we can now copy the Line and Details with the next x++ coding.

insert_recordset line (HeaderRefRecId Group, TemporaryRecId)
    select _headerRecIdNew, Group, RecId from lineSource
        where lineSource.RecId == _headerRecIdSource;

insert_recordset detail (User, LineRefRecId)
    select User from detailSource
    join RecId from line
        where line.TemporaryRecId == detailsSource.LineRefRecId
           && line.HeaderRefRecId == _headerRecIdNew;

This coding is returning the next new records in the tables:

Line

HeaderRefRecIdGroupTemporaryRecIdRecId
103Accountant201205
103Sales clerk202206
103Sales manager203207

After the first insert_recordset command, the new Line records has all the information to copy the several lines in one go. The TemporaryRecId values are used to link with the source detail records. In addition, the new created RecId can be used as detail reference record Id.

Detail

LineRefRecIdUserRecId
205User 1307
205User 2308
206User 3309
206User 1310
207User 4311
207User 5312

As we don’t need the TemporaryRecId values after the copy process, we can clear the information; also with a single SQL call.

update_recordset line setting TemporaryRecId = 0
    where line.HeaderRefRecId == _headerRecIdNew;

In a certain process, the first approach took almost a minute to copy larger record sets. After replacing this with the insert_recordset approach, the execution time was brought back to just a few seconds.

Comments

Post a Comment