Servoy Tutorial: Optimizing Code Performance

    Servoy Tutorial: Optimizing Code Performance

    Servoy Tutorial Photo Credit: Ben Heine via Compfight

    This is a Servoy tutorial on how to optimize code performance. A while back, I had written a complex calculation engine that could accept a multitude of parameters and compute results from the various permutations and combinations. This meant, for example, that a single method that might be computing total cost for a particular material, might be called several hundred times. The calculation engine was extremely large and complex, with hundred’s of methods computing different values from various foundsets of records. As more parameters were driven through the calculation engine, serious performance issues began to surface. For example, in a simple scenario, the calculation engine could compute the result in under 500ms, but add a lot more parameters, indicative of a real-world analysis scenario for the target industry, and suddenly the computation took 8700ms.

    My first reaction to the news was that a large number of methods must be highly inefficient, and that they were going to have to be identified and re-written. However, after carefully identifying the problems using the Servoy profiler, a pattern to the inefficiencies was identified. It turned out to be some very surprising lessons in how to write better Servoy code and those lessons are documented in this article. The task of optimizing the calculation engine was very simple using the techniques presented here, and I am happy to report that even the complex scenario that used to take 8700ms, ended up computing in 1000ms, an 89% performance improvement.

    Take my advice, learn these techniques well; it can save you some embarrassment down the road.

    1. Servoy loads records in 200 record chunks

    You know this one, but do you really understand the implications? When you grab a foundset in Servoy, and even call loadAllRecords(), Servoy only loads the first 200 records into the foundset from the table. If your table actually consists of 100,000 records, you are going to be surprised if you write a loop to compute the total cost for all the records like this:

    First of all, the loadAllRecords() only loaded the first 200 records from the 100,000 in the table, so your method is going to compute the total cost from only the first 200 records. If you rewrite this method to use the total number of records in the table, calling databaseManager.getFoundSetCount() for example, your loop will process all the records and compute the correct total as shown in the next example.

    The problem is, however, that this method will take 23503ms to process the 100,000 records (depending on CPU speed). Why? Servoy is fetching the record from the foundset at the getRecord() call. If the next record being retrieved is not in the 200 record chunk that Servoy has already fetched, then it has to fetch the next 200 record chunk and return the record. It continues fetching chunks until all 100,000 records have been retrieved and the loop is done. This process turns out to be very inefficient. By simply rewriting the method so that all the records are retrieved at one time, you can reduce the 23503ms to 257ms. That, my friends, is a massive improvement.

    Okay, so we know that we need to load all the records into the foundset at one time, and not to rely on the Servoy loadAllRecords() call. However, there is still more that we can do to speed up this method, which brings us to the next point.

    2. Looping through foundsets

    Let’s ignore for a moment Servoy foundsets, and just think about looping through another JavaScript collection, like an array. If our array has 100,000 elements in it, and we loop through it using array.length, it takes us 86ms to loop through all the array elements.

    It turns out that if we first cache the array.length value, and then loop though the array elements using the cached length, our loop can run in 66ms.

    So why is this faster? Well, it turns out that it will run faster using the cached value, because JavaScript does not have to compute the length of the array, on each iteration of the loop. Now, you may be thinking, well, the difference between the cached and the non-cached loop is only 20ms, so big deal! Well, let me take you back to my original problem; if you can save 20ms on every method, and there are hundreds of methods, and they are called hundreds of times, it adds up to a huge difference. Besides, we are talking about optimizations here, so lets do it right the first time, not when someone finds the bottleneck during stress testing.

    If you read some of the JavaScript books out there, you learn that JavaScript can execute a for loop faster counting down to zero instead of counting up to a number it needs to check each time. So, if we loop down, regardless if we cache the array.length or not, our test loop will run at 62ms.

    If we studied our JavaScript thoroughly, then we also know that JavaScript can do a while loop faster than a for loop. So, using it in our example, we can iterate through the 100,000 elements in the array in 51ms.

    The final results for the various array tests are shown in Figure 1.

    Servoy Tutorial : Figure1

    Figure 1 – Array test results

    Okay, brilliant, so we can save 35ms iterating through our array using a cached while loop, achieving a 41% improvement over our 86ms standard approach. Great! So, now lets apply what we learned to Servoy, and see what happens when we loop through a foundset using the exact same techniques we did for the arrays. In this case, we need to use getRecord() to pull the record we need from the foundset in each iteration. A typical method would look like this:

    The final results for the various foundset tests are shown in Figure 2.

    Servoy Tutorial : Figure2

    Figure 2 – Foundset test results

    Well, as you can see from the results in Figure 2, even with Servoy foundsets it is still important to cache the foundset.getSize()in a variable before your for loop(you don’t want it looking up that value with each iteration of the loop), but it doesn’t seem to matter quite as much if you are counting up or counting down.This is probably due to the fact that it takes a substantial amount of time for getRecord() to execute, so savings obtained from JavaScript counting down are lost. Furthermore, caching the foundset size before the loop can have a much bigger affect then the summary shown in Figure 2. With repeated testing, sometimes the loop using no cache can be more than 100ms greater than the loop that does use the cache. I’m not sure of the reasons behind this, but Figure 2 shows the best result for the loop with no cache.

    So, in summary, you need to loop through a foundset using a cached value, like this:

    Good, we know a little more. But how about the foundset itself? Suppose we have many methods, each doing something different with a foundset from the same table. One method might be computing cost, the next might be computing time, another could be totaling values. Surely we don’t want to create the foundset in each method that runs, get the max records in the table using the databaseManager.getFoundsetCount() call (an expensive operation), load all the records into the foundset, and then cache the max record count for an optimized for loop. As well, sometimes methods omit records from the foundset, or alter the foundset, so you can’t just pass it around from one method to the next and hope for the best. The solution to the problem is using a cache.

    3. Cache is King

    Its an old cliche, but it can have a dramatic impact on overall performance if used wisely. We have already seen that caching the foundset size before the for loop is important, but computing the foundset size and the foundset itself, can also be cached and accessed by methods that need it. The cache can be setup when the main method initializes, taking a snapshot of the foundset at that point in time, so that all methods can access it. Later the cache can be cleared freeing the memory, ready for the next time when the process starts all over. To learn more about how to use an object for a global cache, refer to the Servoy tutorial in the Related Posts below.

    Keep in mind that the global cache can be used for more than just foundsets. You can extend it to store any kind of values by key. This can also improve performance significantly. For example, in my estimating engine, methods had to lookup costs for specific materials and compute markup using complex pricing rules. Adding the computed markup to the cache by material id (in this case the key), and retrieving it from the cache, rather than recalculating when analyzing additional combinations of parameters, further improved performance.

    Even if you don’t want to use a global cache object like I show here, at least use function memoization. This uses a local object in a function to build a cache and can provide a speed improvement. Suppose you have a function that takes in an item id and will then perform a bunch of calculations. If you performed the calculation for a given item already, function memoization allows you to grab the previously computed result and return it, instantly, rather than running through hundreds of lines of code that will compute the same result all over again. To learn more about how to use function memoization, refer to the Servoy tutorial in the Related Posts below.

    It is hard to quantify the performance improvements of this type of approach, as it will be unique to each situation where it is implemented. Being able to avoid recomputing the foundset count in each method, ensuring that each method uses the entire foundset, loading the entire foundset only one time for all methods, and using the cached foundset count for your loops, will give you a substantial performance boost overall.

    Bottom-line, use a cache, whenever and wherever you can; cache is definitely king when it comes to improving code performance.

    4. Servoy lookups

    By now you are either excited to start writing more efficient code, or you are bored out of your mind and don’t know how you made it this far. In any case, we are not done. There is one more performance tip that I will share with you, and that has to do with using the Servoy find(), and how it compares in performance to a relational lookup or a SQL query.

    A typical Servoy find() should look something like this:

    By the way, the reason for the dual find(), for those of you that don’t know, is that sometimes the first time find() is called, Servoy fails to go into the find mode and will return false. I think this had to do with the foundset not being loaded, or maybe locked, but in any case, the second find() will put it into find mode so that it executes properly. Now, the more complex the find (more criteria you add or additional find request records you create), the longer it will take to execute.

    A relational lookup looks something like this:

    In a relational lookup, you set a global(s) to the primary key (and/or foreign keys) and then test to ensure you got the record(s) you needed. The relation itself is easy to setup, as shown in this example:

    Servoy Tutorial : Figure3

    Figure 3 – Setting up a Servoy global relation

    The final option is to use a SQL query to locate the records of interest. In many instances, this will be the preferred method, particularly if the search criteria is rather complex and in related tables. In these situations, it will not even make sense to use a Servoy find() or a relational lookup. A SQL query can simply be done like this:

    So, how do they all compare to one another? Which one is faster? Well, Servoy find() is the slowest, easily 2x as slow as either of the other two methods. The actual performance of the find() depends on the complexity of the criteria, but below I show the results of a simple test using a 100,000 records.

    Servoy Tutorial : Figure4

    Figure 4 – Lookup methods compared

    Again, this is a simple test, so there is only a 2ms difference between the approaches, but when more complex criteria are involved, the difference can be very dramatic. It is not uncommon to see the Servoy find() take 50x as long to locate the records that a relational lookup or SQL query can do in short order. In my estimating engine, eliminating Servoy finds() from my methods was absolutely necessary in achieving maximum performance. If you don’t watch this one, it can be a killer, when you least expect it.

    5. Others

    There are other performance improvements that can be made, like avoiding the use of Servoy calculations in table views, using stored calculations vs un-stored calculations, writing modular code, using efficient algorithms, etc. But, these were not problems affecting my calculation engine, where the story started. So, I am going to leave those for someone else to discuss. Besides, I can tell your nodding off already. That concludes this Servoy tutorial. Whew!

    Gary Dotzlaw

    Gary Dotzlaw

    CEO at Dotzlaw Consulting
    Gary Dotzlaw has 20+ years of experience as a professional software developer and has worked with over 100 companies throughout the USA and Canada. Gary has extensive qualifications in all facets of the project life cycle, including initial feasibility analysis, conceptual design, technical design, development, implementation and deployment. Gary is an expert Servoy Developer, with extensive large-commercial project experience, and has written numerous advanced tutorials on Servoy development.