What to choose to achieve loading speed

It is important to use the right tool for the job. As a developer there is often the choice between to use a technology that let you do anything at the cost of you must also take care of everything. Or use a framework that take care of most details. The level of abstraction is raised. Bold for Delphi is such framework.

The header of todays post says Loading speed. Performance is often important in applications. Here is a simple list of techniques of show data from a database with comments about speed.

1. Using OCL with all dependent objects in object-space (RAM) and PS set to False. I used to call this warm objects. In my personal experience there is nothing that can beat that in speed. The GUI is very fast updated.
2. Handwritten SQL versus Bold is like assembler versus C or C++. When written properly it can be very fast. But it is also easy to write slow SQL with sub-queries, introduce unnecessary complexity etc.
3. Using OCL with dependent objects not in object-space (RAM) and PS set to True. This is cold objects. In some cases it can have equal performance as well written SQL. Note there is some limitations of the expression as derived attributes and links cannot be used. The result must be a collection of Bold objects.
4. Using OCL with all dependent objects not in objects in object-space (RAM) and PS set to False and prefetch is used to load objects. See the explanation what prefetch is.
5. Using OCL with all dependent not in objects in object-space (RAM) and PS set to False and prefetch is not used. This is lazy fetch. One SQL call is generated for every object loaded and this can be a bit slower.

One thing to be noted is that the database cache has big impact of the performance. The second time the same SQL is executed the data is already in the cache and is delivered faster.

When Bold load data and PS is set to False Bold evaluate and generate the needed SQL to load data in object space. The next time Bold need the same object it is already in memory and the speed gained is very high. It is number one at my list above. The problem is when new data is to be loaded all the time.

Prefetch
Bold contains some methods to avoid lazy fetch, EnsureObjects and FetchLinksWithObjects. The problem is then the model grow an by just reading a derived member may start a chain of lazy fetches that could be hard to anticipate.

One solution we at Attracs use with success is prefetching. When loading bigger datastructures from database it is often many instances of the same class. It is much more effective to load many instances in one SQL call compared to one SQL call per instance. We use some kind of prefetch language to archieve this.

A prefetch expression can look like this:

(activeCustomers, (activeOrders, invoice))

The precondition in this fabricated case is class CustomerPool that have a multilink ActiveCustomers that have the class Customer in the other end. The Customer have a multilink to Order class by the multilink activeOrders. And the Order class have a link to Invoice class. by doing a prefetch on this then all Customers is loaded in one SQL and the same with Order and Invoice class. The result is better user experience.

Some people may think that the syntax of this prefetch language is a bit funny. It should be noted that prefetch is not part of the Bold framework (yet). It is an in house technique to get better performance.

Now we also have a method that generate a prefetch expression from an OCL expression. This make it much easier as the prefetches can adapt from changes in model. The result is more dynamic and less hard coding!