Testing Performance Business Central
There are a couple of ways of testing performance in Business Central, you of cause have the manual testing where you go through your features using the UI, this way of testing can be very slow 🐌 however you should always perform this type of testing since it will give the most reliable picture of how everything is running. Next, you have the ability to use Applications insight which can monitor your whole application and is a very powerful tool, however, this is more for monitoring your Business Central to make sure everything runs as expected, and not really a testing tool. The last way of testing your extension is to use unit tests, with functions specifically written to be able to test the performance of your code. In this post, we will focus on the last method of testing, by using Unit Tests. All the source code can be found here: https://dfredborg.visualstudio.com/Business%20Central/_git/Business%20Central?version=GBTestingPerformance
Writing Unit Tests to test performance it pretty straight forward, because Microsoft has given us a way to check our SessionInformation and inside the SessionInformation they have given us two methods one called SqlStatementsExecuted() and the other called SqlRowsRead() which both gives us a way to see how much data is sent between the user interface and the SQL database. ❤
The SqlRowsRead and the SqlStatementsExecuted will tell you the total amount of either reads or executions since the start of the session. So to make any kind of sense you need to know the number of calls that your code executes, to do this you must call the functions before and after your code and get the difference, a simple example could be to see how many reads goes into a loop vs. a GET; I know it is stupid but it is just to keep it simple 😉
[Test]
procedure SQlReadFindSet()
var
Customer: Record Customer;
SqlRowsReadBefore: BigInteger;
SqlRowsReadAfter: BigInteger;
begin
SqlRowsReadBefore := SessionInformation.SqlRowsRead();
if Customer.FindSet() then
repeat
until Customer.Next() = 0;
SqlRowsReadAfter := SessionInformation.SqlRowsRead();
Error(format(SqlRowsReadAfter - SqlRowsReadBefore));
end;
[Test]
procedure SQlReadGet()
var
Customer: Record Customer;
SqlRowsReadBefore: BigInteger;
SqlRowsReadAfter: BigInteger;
begin
SqlRowsReadBefore := SessionInformation.SqlRowsRead();
Customer.Get('10000');
SqlRowsReadAfter := SessionInformation.SqlRowsRead();
Error(format(SqlRowsReadAfter - SqlRowsReadBefore));
end;
As you would expect then SQLReadGet will return 1 and the SQLReadFindSet will return the number of customers.
Now while this code might not tell us much, it does show us how you can use it. What you would do in the real world is that you would set up a threshold, where you would test that your code does not perform more than a certain limit of reads, and if it does your unit test should fail. ✔
[Test]
procedure SQlReadTestReadLimitGet()
var
Customer: Record Customer;
SqlRowsReadBefore: BigInteger;
SqlRowsReadAfter: BigInteger;
begin
SqlRowsReadBefore := SessionInformation.SqlRowsRead();
Customer.Get('10000');
SqlRowsReadAfter := SessionInformation.SqlRowsRead();
if SqlRowsReadAfter - SqlRowsReadBefore > 1 then
Error('Too many reads: ' + format(SqlRowsReadAfter - SqlRowsReadBefore));
end;
The SqlStatementsExecuted() works in the same way as the SqlRowRead() with the only difference being that it counts the number of statements executed.
[Test]
procedure SQlExcecuteValidate()
var
Customer: Record Customer;
SqlExecutedBefore: BigInteger;
SqlExecutedAfter: BigInteger;
begin
SqlExecutedBefore := SessionInformation.SqlStatementsExecuted();
Customer.Get('10000');
Customer.Validate(Name, 'NewNameValidate');
Customer.modify(true);
SqlExecutedAfter := SessionInformation.SqlStatementsExecuted();
Error(format(SqlExecutedAfter - SqlExecutedBefore));
end;
So as you can see then you can use unit tests to make sure that your code runs as smoothly as possible, and while it might be difficult to figure out what your threshold should be, once you figure it out, it is a quick way to make sure that your code does not make unnecessary reads and executions. Well, that was it for this Thursday Nugget follow up until next time stay safe. 😷
You can find Microsofts documentation here: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/performance/performance-developer#testing-and-validating-performance