Archives

Drop Early, Drop Often

Don't wait until the end of your QlikView load script to drop temporary tables—get rid of them as soon as possible and reap rewards in the form of memory, CPU, and time savings!

 
This is going to be a short post, because my advice to you today is extremely simple: drop your temporary tables as early as possible. I have to confess, for many years I was guilty of not following this advice myself. One of my favorite development techniques in extract/transform layers was to create an iterative process at the end of the load script to simultaneously store/drop all loaded tables. Recently, however, I ran into a constraint. I needed to load several large tables and simply ran out of RAM. I realized, however, that I can make QlikView release RAM by dropping tables in the model that I was not actually using anymore. This easy technique allowed my script to successfully execute, where it was failing before. However, even if your situation is not this extreme, this painless technique will allow you to save RAM and CPU cycles, which is great even if you have a private development box, but is particularly nice if you happen to be sharing a development server. By creating lean load scripts, you will become a better server co-tenant, and everyone wins. As an added bonus, QlikView performs more snappily when it has less tables in RAM to manage, and you script will likely finish sooner.
 
Here are before/after performance stats on an application that I improved using the "drop early" technique. This is not the one that I mentioned was failing earlier, and is not a particularly dramatic example. Nor is it perfect, because even in the old model I had dropped some tables early. However, you can see some significant improvements here. Most obvious are the improvements in RAM usage and load time. Both maximum and average RAM usage is much lower in the "drop early" version than in the "drop late." And we were able to shave 4 minutes off the reload time, while sacrificing absolutely nothing.
 
RAM

 
CPU

 
Happy Qliking!
This entry was posted in Development, Tips & Tricks and tagged , , . Bookmark the permalink.

4 Responses to Drop Early, Drop Often

  1. Aaron Couron says:

    In addition to the RAM and CPU savings, you also seemed to shave about 25% off the reload time. Such a simple technique to reap such a large reward. I have switched between dropping at end and during almost on a whim (or on the time I have available to develop) so now that you have quantified this I think I need to drop tables ASAP from now on. Great post.

  2. Bruno Decruynaere says:

    Wouldn’t it be good if also mapping tables could be dropped to free memory? In some applications we need tons of large mapping tables and these are only dropped when script finishes. In many cases these tables are used only once so could be dropped much earlier, but as far as I know this is not possible. As you demonstrate it could help a lot in scrip run performance.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify via email when new comments are added