Optimized .QVD Confusion

[#1]

Hello QlikCommunity,

I have been researching the best practices of .QVD use, and have been left with one question regarding the optimization of .QVD loads. When it is said that a .QVD is optimized, does this mean the .QVW that is creating the .QVD will create it more efficiently and quicker? Or does an optimized .QVD mean that the .QVW absorbing the .QVD's will do so more efficiently and quicker?

It was my assumption that an optimized .QVD was only quicker when being created (or loaded to incrementally). If this is the case, a .QVD that is recreated nightly would not necessarily benefit from optimization. But rather it would be better to move the data transformations to the .QVD so that the application .QVW does not have to do them.

Let me know if my thoughts/questions aren't clear.

Thank you for the help QlikCommunity!

-Chris

[userid128223]

what is time and performance difference between an optimized qvd vs non optimized.

[stevedark]

I have read that it can be up to 100 times quicker to be optimised. This

will depend on the file in question and a number of other factors. The

easiest way to check is to benchmark it. Try doing the following:

LOAD

• **

FROM MyQVD.qvd (qvd);

Then

LOAD

• 1 as BreakOptimised,*

• **

FROM MyQVD.qvd (qvd);

That will then show you the difference in load time.

- Steve

[#4]

Hi Steve,

(or Rob, Oleg, Henric, anyone?)

Do you know if Qlik did any optimizations to the non-optimized QVD LOAD in recent versions? (Currently using 11.2 SR3 - yeah, I should update...)

I tried to intentionally de-optimize a QVD LOAD to test a hypothesis by one of my colleagues, but both the optimized and non-optimized QVD LOADs are approximately equally fast (no significant difference on 8 test runs, all in the 16-24 second range for approx. 10mln. records and both optimized ('qvd optimized' in log) and non-optimized varying by 5-6 seconds between runs), far off from your '100 times quicker' claim... The only way to slow it down was by moving a WHERE clause to a preceding LOAD (times shot up to 50+ seconds... there goes my favorite QV construct ).

Regards,

Martijn ter Schegget

PS: I've tried the following ways to de-optimize the LOAD:

• adding the '1 as BreakOptimized' column from your example
• calculate a new field from 2 fields: 'time(EndTime - StartTime) as Duration'
• insert WHERE-clause using 2 fields: 'WHERE LAST_MODIFIED_AT < CREATED_AT + 1'

Each of these at least suppresses the '(qvd optimized)' notification in the logs, but doesn't significantly slow things down. So it isn't specific to the '1 as BreakOptimized' version.

[stevedark]

Improvements do sometimes sneak in under the radar.  I know I avoided LEFT JOINs from QVD for a long time as they killed performance - basically a RESIDENT and a NON OPTIMIZED load at the same time.  However, I was surprised recently when I did one out of necessity and the joined data loaded very quickly.

When I get time I will have to go back and re-benchmark some loads.  I always prefer to see the Optimized Load text appear though, whether the load time is not that different or not!

Steve