Monday, June 28, 2010

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!

18 comments:

  1. Hi, Roland.

    1) Prefetch it is use
    (TLogPrList(TBoldSystem.DefaultSystem.ClassByExpressionName['LogPr']).EnsureObjects;
    and ...FetchLinksWithObjects)
    or not?

    2) (activeCustomers, (activeOrders, invoice)) it is OCL?

    Regards, Yuri.

    ReplyDelete
  2. Hi Yuri!
    1. Yes I should mention that in original Bold EnsureObjects and FetchLinksWithObjects can be used for prefetch. It is just that with more complicated object structures this will be complicated and hard to adapt after changes in the model. Imagine a list with a class A. This contains a derived attribute AA that will read an derived link LB and persistent link LC. LB also read persistent attribute AC. Now in your code if you want to read A.LB of all instances of A in your list, you must remember to prefetch AC for all instances. If you read A.AA on all instances you must prefetch A.LC and A.AC. Things will quickly be very complicated and hard to maintain for bogger models.

    2. Not this is not OCL. We don't have a real name for it. This is just a prefetch expression. The expression describe what objects to load in one SQL call to avoid a lazy fetch.

    ReplyDelete
    Replies
    1. Perhaps it could be called "RIL Expression" ? =)

      Delete
  3. Thank you for answer Roland.

    There was interesting learn little more of this house technique - prefetch, рossible in following post

    >It should be noted that prefetch is not part of the Bold framework (yet)
    Why yet?

    Regards.

    ReplyDelete
  4. The prefetch engine is indeed advanced. But it is not written by me, so I haven't the full knowledge how it works internally. But I think it builds a parse tree from the prefetch expression. From this it could build lists, one for each table in database if that was found in the prefetch. The goal is to execute as few SQL calls as possible and load many objects in each SQL call. On top of that an OCL expression could be translated to an prefetch expression.
    Not so good explanation maybe, but this is not open source so I cannot reveal too much.

    I think the reason that this is not part of our Bold version yet is that there is still some things that are application specific. It should be generic to fit in Bold.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. (fixed spelling...)

      Regarding the the prefetch engine,

      The prefetch makes extensive use of the inbuilt FetchLinksWithObjects method.

      It also uses additional lists to keep track of what to be prefetched as to avoid getting stuck in recursive structure definitions in the prefetch expression. I no longer remember if these extra lists required modification to the source code. If so it would probably affect BoldSystem.pas.

      The "root" of prefetch expressions (you can have several) is always the calling object. For this reason it's preferable to use prefetch from only dedicated classes ("controllers" of groups of objects) to be tested after model changes.

      The prefetch method can optionally use lame or "forceful" prefetch, meaning that entering "areas of already loaded objects" won't terminate the traversing of the object structure to load. If this "force" option is set to false, then even the prefetch itself is optimized (that is, terminating at the first loaded object in the structure defined by the expression).

      This is all from the top of my head, since this was coded nearly a decade ago.

      It sounds like a brilliant idea to dynamically produce prefetch expressions based on ocl-expression! It implies that the (ocl) prefetch expressions also can be tested through the ocl-evaluator designed by Christophe Floury?

      // Rolf Lampa

      Delete
    3. Hi Rolf!

      Jan Norden wrote this new addition for prefetch for about 4 years ago. And the real power came when we moved a lot of mebers from codederived to OCL. With OCL all the information is in the model to automatically generate an efficient prefetch expression. As there could be a chain of derived members calling each other the expression can be big. There is also an optimization pass trying to make it shorter before the actual execution.

      But for those members that are still written in code we have to see what data is needed and add that manually to the model as a SpanExpression tag.

      I also remember after we got live with this the users reports random crashes. We tried hard to reproduce it in test environment but failed. Jan also worked a lot with this bug, and he finally found it. It was in Bolds OCL parser. This new prefetch engine generate much longer OCL expressions than before and this trigger the bug. I don't remember the exact preconditions but it was something when the expressions have several parts divided by and. So for example:

      if (price > 0) and isValid then
      'Fine'
      else
      'Not fine'
      endif

      In this case if price = 0 then isValid is never evaluated as the whole expression is False. This caused some dangling pointer to be used. Sometimes the memory was valid, sometimes not.

      Delete
  5. Hi friends,
    Really amazing blog post.I saw and read your blog,This site is useful to
    Delphi.I know that it will help me in my own Delphi Blog.I think it may be relevant.
    Thank You
    http://frankieespinoza.livejournal.com/1181.html

    ReplyDelete
  6. Hi,
    why Bold for Delphi, this Methuselah development tool?
    What about MS Visual Studio?
    What about java?
    What about .NET?
    Do you really want to use this tool, that is not platform independet?

    I'm very surprised!!!

    I was using BfD from 1999 a couple of years, it was my favorite development tool.
    For 5 years I'm retired and had lost touch with it.
    I feel happy to read, that BfD is still active and Christophe Floury is providing this blog.
    Best regards to Christophe.
    You can email me: my@artiques.de

    Christel Rotter

    ReplyDelete
  7. Hi Christel!
    To answer you question why we don't use .NET or Java :-) Well we begin use Delphi and Bold to begin creating the application, and it is a efficient tool! I have not seen such power and simplicity on other frameworks. To be platform independent is not so important for us and btw .NET is only plaform independent as long as you don't call Win API.

    PS. I'm the author of this Blog. I know Christophe Floury as a consultant for us. A very nice guy!

    Roland Bengtsson

    ReplyDelete
  8. Hi Roland,
    thanks for the quick response.
    It is unbelievable, for more than 10 year I've had the same experience with Bold as you:
    I have not seen such power and simplicity on other frameworks!
    I never understood, why so few developers make use of Bold.
    I do not understand, why Bold is no longer included in Delphi. Developers have no interest?
    Fortunately, not all developers think so.

    Regards
    Christel Rotter

    ReplyDelete
  9. Yes, Bold is great but it has some disadvantages. The biggest is that you cannot buy it anymore due to sloppy management of Borland/CodeGear/Embarcadero! ...

    Other is the memory management, it is easy to load a structure of objects but not so easy to unload them from memory. Other that I can think of may be outdated GUI components. But with Attracs we have fixed that by making Devexpress components boldaware :)

    Roland Bengtsson

    ReplyDelete
  10. I am working on a Bold for Delphi (2011 aka XE).

    Any interest to help?

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Hi Anonymous!

    I'm happy that someone start the work to make Bold compatible with the latest Bold and I wish the project success. But we at Attracs have modified our Bold version a lot with bugfixes and optimizations. So of course we want to continue use that.

    In a really sunny and perfect world Embarcadero should release Bold as OpenSource. A new project will quickly be set up at SourceForge.net and Bold developers around the world would start to make one unified version of Bold. Delphi programmers can easily test Bold and see what it can do for them. Bold can be popular!
    As a sideeffect less people may change from Delphi to other platforms (dotnet). It may also generate more sales of Delphi licenses.

    But for your original question if I want to help. Currently we do not have time to spend on things like this. I can of course help you on my free time. But I have family and house so I don't think I have time left for a such time consuming project.

    In Attracs we plan to make our Bold version compatible with Unicode when Embarcadero release a 64-bit compiler. This is because Unicode take more memory and already now we sometimes hit the 2 GB limit of a process.

    Regards
    Roland Bengtsson

    ReplyDelete