One of the most effective ways to review an optimum code is to literally measure the execution time. Dynamics AX offers several tools, and as we have seen previously, one of them is the winAPI class, which grants functionality such as copying files between routes, reading files, opening links from the code and in this case measuring times.
If we review the information below we will see that by writing a simple script in a job, we can test it by counting from one to a hundred within a cycle, and pausing for 1000 milliseconds, to get a certain time on the screen.
The static method we use counts the time from the start of the cycle to the end. The striking thing about this example might be the pause we use in between.
The same method can be written in many ways, obtaining different optimization results. We have created two find methods to try to obtain different times:
Recall that the result of these examples will depend exclusively on the number of customer groups in the environment where we are working and may vary depending on the existence of an appropriate key. There is always more than one way to write the same logic, but that does not mean that one is better than another, it means that there is always a more optimal way of doing things and it is that optimization that we should aim for.
Therefore, to test the optimization of queries in our developments there will always be three basic steps:
- An appropriate construction of the database structure, which will guarantee a solid relationship between tables and the existence of corresponding primary and foreign key components.
- Microsoft´s good practices recommend building two main types of methods at all times, find and exist. The first as we have seen is a selection of a single record that returns the combination of data contained in it. Although a method exists, it is a selection of a single record which returns a boolean indicating the existence of this. If we have a well-constructed primary key, then this good practice ceases to be a recommendation and becomes an obligation.
- Measuring the times is a way to estimate which logic is more optimal. The effectiveness of a query is determined by its execution time.
Not all tables will be the same in their construction, due to their nature. For example, a temporary key does not need a primary key and is very likely not to contain any keys at all. However, it is highly recommended to calculate the data query and insertion times for this, since the logic that we use can always be optimized.
The use of intermediate tables is also recommended when complex data processing is required. In these instances, it is also valid to calculate the execution times as a valid optimization alternative.
Therefore, we can see that this technique is scalable in every sense.