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
|
ID
|
Description
|
RecId
|
|
AAA
|
Description AAA
|
101
|
|
BBB
|
Description BBB
|
102
|
|
CCC
|
Description CCC
|
103
|
Line
|
HeaderRefRecId
|
Group
|
RecId
|
|
101
|
Accountant
|
201
|
|
101
|
Sales clerk
|
202
|
|
101
|
Sales manager
|
203
|
|
102
|
Controller
|
204
|
Detail
|
LineRefRecId
|
User
|
RecId
|
|
201
|
User 1
|
301
|
|
201
|
User 2
|
302
|
|
202
|
User 3
|
303
|
|
202
|
User 1
|
304
|
|
203
|
User 4
|
305
|
|
203
|
User 5
|
306
|
|
204
|
User 4
|
307
|
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
|
HeaderRefRecId
|
Group
|
TemporaryRecId
|
RecId
|
|
101
|
Accountant
|
0
|
201
|
|
101
|
Sales clerk
|
0
|
202
|
|
101
|
Sales manager
|
0
|
203
|
|
102
|
Controller
|
0
|
204
|
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
|
HeaderRefRecId
|
Group
|
TemporaryRecId
|
RecId
|
|
103
|
Accountant
|
201
|
205
|
|
103
|
Sales clerk
|
202
|
206
|
|
103
|
Sales manager
|
203
|
207
|
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
|
LineRefRecId
|
User
|
RecId
|
|
205
|
User 1
|
307
|
|
205
|
User 2
|
308
|
|
206
|
User 3
|
309
|
|
206
|
User 1
|
310
|
|
207
|
User 4
|
311
|
|
207
|
User 5
|
312
|
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.
