(Edit: Oh, and TEST. I'm putting this at the top because it's very important. What you THINK is fastest may well not be fastest. What we tell you is fastest most of the time may not be fastest in your case. Try different approaches and SEE which one is fastest. I knocked my load time in half on a recent application by just continually playing with different ways of loading the data, even the ways I thought would be slower. Some of them were faster for my specific case, not slower. Everyone's situation is a little bit different.)
When loading from your QVD, make sure you're getting an optimized load. Doing so often requires some strange gyrations, because the only condition you can put on an optimized load is a single where exists(). So if you want something like this:
[MyTable]:
LOAD a bunch of fields
FROM MyData.qvd (QVD)
WHERE "Status" = 'Active'
AND "Date" >= yearstart(today(),-1)
AND match("Product",'A','B','C');
It will often be faster to break this up to get an optimized load. First, what's your most restrictive condition? That's the one we want to focus on first. Let's say it's the date condition. We can generate all the dates that we care about, and do an exists() on that. The other conditions can be added as an inner join after the fact. So something like this:
[MyTable]:
LOAD date(today()+1-recno()) as "Date"
AUTOGENERATE today() - yearstart(today(),-1) + 1
;
INNER JOIN ([My Table])
LOAD a bunch of fields
FROM MyData.qvd (QVD)
WHERE exists("Date")
;
INNER JOIN ([MyTable])
LOAD * INLINE [
Status, Product
Active, A
Active, B
Active, C
];
In my experience, the second version, while significantly more complicated, is likely to execute faster. On the other hand, it may also use more memory because it is temporarily loading records that you will later throw out. So which version you use may depend on whether your CPU time or RAM is more plentiful. Experiment to see what works best for you.
Miguel Angel Baeyens wrote:3.- Avoid IF() conditionals in any object expression, use instead set analysis.
And where reasonable, consider object model changes instead of even set analysis. In my experience, object model solutions are fastest of all, then set analysis solutions, and then everything else. And from a maintenance standpoint, I prefer seeing the complexity in my load script instead of in my tables. I'm not sure why it matters to me, since in our shop the same people are responsible for both script and charts. But in some shops, this might actually be important.
Miguel Angel Baeyens wrote:9.- Avoid RESIDENT loads. If needed, load twice from a QVD.
This may seem counterintuitive, but yes, in many or maybe most case, it seems that loading again from the QVD is faster than loading from a resident table. I've seen exceptions, but those are the exceptions, not the rule. It's a strange rule, but something to keep in mind for certain.
That's not to say "avoid resident loads" - I use them all the time. More like "if you have a choice between a resident load and an optimized QVD load, in my experience the optimized QVD load is usually faster". The exceptions tend to be when the resident table has already been significantly reduced in size and number of columns, I believe.
Ah, didn't realize this had been improved in version 10. We haven't upgraded yet. I'll have to keep an eye out for that.
CheenuJanakiram wrote:3. If needed, iron out synthetic keys (even create your own link tables) if you get them, as this might also minimise reload time. Although, QV can easily deal with synkeys, by taking this process in your hands and incorporating it in the script, you will prevent longer reload times.
I'll strongly emphasize the "if needed" part of this. IF it is appropriate for your databases to be linked by two fields, it is appropriate for that link to be modeled using a synthetic key. In my experience, as long as your data model is correct, converting synthetic keys to their link table equivalents does nothing but waste script time and use a TINY (basically negligible, but I thought I'd mention it) bit more memory and CPU time in the application itself.
To me, the main reason to remove synthetic keys in a good data model is simply practice. You should understand what the synthetic key IS, and a good way to get that understanding is to model the connection yourself rather than relying on QlikView to do it for you automatically. Also, people less experienced with data modeling and/or with QlikView may often find that synthetic keys are a symptom of an underlying data model problem that SHOULD be fixed. But in a case like that, merely replacing the syntheic key with a link table equivalent is not going to fix the underlying data problem. You have to think about it a bit more deeply than that.
More information on synthetic keys here:
http://community.qlik.com/forums/t/31028.aspx
CheenuJanakiram wrote:5. If need to use link field in count computation, then enter a "1 as Linkfieldcounter" in the relevant table. On front-end, go sum(Linkfieldcounter), you will get the same result as count(counterID) and it is less expensive to do a sum over 1 values.
I've seen tests go both ways in this regard. It seems to depend on the QlikView version. I haven't tested version 10, and don't remember which is faster in version 9. I typically do a count(distinct LinkField), but strongly suspect that's the slowest option of all. Basically, if you need a count of the keys, and the expression is slow, try both a sum() and a count(), and use the faster one for your version.
.png "Luminary Alumni"){kind=icon}
.png "Miguel_Angel_Baeyens"){kind=avatar}
