Is an optimized LOAD with a RESIDENT afterwards faster?

[datanibbler]

Hi,

I have read some articles on optimized and unoptimized qvd_loading, but now I have a specific question:

=> I am just looking at an app that, for some reason, loads about 200mio records from a qvd in an unoptimized LOAD - that is apparently because there is one keyfield being constructed from two separate fields in that LOAD, otherwise it is a plain LOAD that could be optimized.

Is it worthwhile doing an optimized LOAD on this and then doing a RESIDENT LOAD where the keyfield can be constructed? Would that still be faster?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hmm ... looking closely, the LOAD is still not optimized - strange. Might be because there is an

>> AUTOGENERATE 0 <<

before to initiate a table and this LOAD is merely concatenated - but well, I have separated it, so it's a separate LOAD now. It still seems to be a lot faster than before, but it's strange - apart from the filepath, which is encoded in a variable as a relative path (a developing Standard here) and the QUALIFY beforehand which I have just inserted to Keep it from forming a large synthetic key, there is nothing about this LOAD that should Keep it from being an optimized LOAD ... well, there has to be something. Let's see.

[datanibbler]

Ah - I know why - I have overlooked the fact that the LOAD before that does not have a FROM - so that's a preceding LOAD, and in that one another keyfield is generated. So I'll have to separate those ...

[datanibbler]

Hah! Now at last it is optimized. I can do away with that preceding LOAD, I'll put both field_constructions into the same RESIDENT LOAD.

That fixed, there is another issue that might offer potential for optimization: There is a nested IF_clause in what was that preceding LOAD - would it make a difference if I Change that into an IF(AND()) construct? The effect would be the same ...

[Peter_Cammaert]

That depends on whether or not the Script expression evaluator has an optimiser built-in. In a nested IF() the second IF() will never be evaluated if the first one fails. In an AND logical combination, a dumb evaluator may decide to evaluate all clauses before making a decision. A smart one will drop the second operand if the first one evaluates to false.

The theoretical stuff aside, I'm not really sure that you will recover a lot of execution time by modifying this code one way or the other..

[datanibbler]

Thanks Peter!

Okay, I think it's enough - just by optimizing this one LOAD and then doing a RESIDENT, the runtime of that Thing has gone down by approx. 70-80%. That's quite enough for a Friday 😉 I'll just Close this so that no loose Ends remain and then I'll wrap it up.

[Peter_Cammaert]

Have a nice weekend.

[passionate]

Hi Friedrich,

Instead of loading from qvd and then using Resident load.

I would say go for preceeding load which is a good approach.

Regards,

Pankaj

[marcus_sommer]

Hi DataNibbler,

I think it's rather the wrong place to create this key and might be better to create it before in the routine which creates this qvd. Additionally to this it might be useful to implement incremental approaches so that only within the new data the key is created and all old data will be concatenated to them - with a where exists-clause the qvd-load will still be optimized.

- Marcus

[datanibbler]

Hi Marcus,

all These are approaches that I will be looking at going Forward. I have now begun the rather biggish Task of looking through all of our existing QlikView_apps and looking for any potential for improvement and performance-Tuning (it is all on "our own" Server and we are about to take on a rather large new Project which will come with a number of new apps running on it, so doing some cleanup beforehand seems like a good idea 😉 And it's a good way of getting to know all there is so I can find my way around some time soon.

@ Pankaj

A preceding LOAD is what I had before, but that is not optimized and in this Point in the script I'm loading about 200mio records (for whatever reason) from a qvd, so that is quite a difference 😉

P.S.: At the Moment I have two RESIDENT LOADs following that LOAD which I just optimized - that takes away some of the Performance_win of course. But maybe I can do away with one of them by following Marcus'  Proposition of creating one Special field I have one step earlier, when I have ?fewer? records to compute ...

[passionate]

Hi Friedrich,

Are you using any ETl tool before using Qlikview.

If yes, try moving all this to backend i.e in ETL part.

Regards,

Pankaj