Wednesday 16 September 2009

Avoid Resident for large data sets

As part of your training, you were probably taught to us Resident loads to re-use data that has already been loaded. I have used this and taught this for several years.

However, my experience recently with larger data sets is that resident load is very slow!

A much better choice is to re-use a QVD if you have one. In fact, if you write the in memory table to a QVD (using STORE), and then use that QVD for your next load, it is still faster than using Resident!

6 comments:

  1. I definitely second that. I have had that feeling for quite some time but not had the time to actually prove it... It seems lite QlikTech in the quest for better QVD-performance forgot to optimize resident loads in the same matter. Hopefully we will see an improvement in versions to come. (Because in theory it really has to be faster to "read" inside memory than to and from disk once the process is optimized.)

    ReplyDelete
  2. Most of the time when I load from a resident table I'm using a WHERE or GROUP BY to modify the data (because why would you just re-load data you already have). Those options slow down a qvd file load.

    ReplyDelete
  3. Hi Tim,

    You are absolutely right about applying Where/Group By clauses. My experience is that these are still faster (as of v9.0) using QVD than with Resident.

    Quite often, for example in the creation of a key table, I will just re-load data I already have - the QVD optimisation on this use case is way ahead of Resident.

    ReplyDelete
  4. Hi,

    How large is a "large" dataset.

    Thanks,
    QV Newbie

    ReplyDelete
  5. How deep is a hole?

    It depends.

    Just note that this original post pre-dates v10 and resident performance is much better now.

    ReplyDelete
  6. i confirm this ideas, QVD is the faster file in qlikview because it is the native file.
    I have done some test using resident and qvd but i see it is very optimize to use qvd. but my application has 10 millions of rows

    ReplyDelete

Note: only a member of this blog may post a comment.