topic Re: Optimized .QVD Confusion in QlikView

Optimized .QVD Confusion

Fri, 08 Nov 2013 14:28:45 GMT

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

Re: Optimized .QVD Confusion

rbecher — Fri, 08 Nov 2013 14:53:01 GMT

Hi Chris,

I think your're mixing things here. There is no optimzed QVD file format (could be optimized in size by using smaller data types and/or fields with less cardinality althought) but there is an optimized QVD LOAD. Optimized load means the fastest way to load data from a QVD file into memory (and QVW).

- Ralf

Re: Optimized .QVD Confusion

peter_turner — Fri, 08 Nov 2013 14:55:49 GMT

Hi Chris,

The optimised QVD is a method for loading the QVD into your QVW in the fastest way possible.

There are rules associated with this, and some script operations or conditions will stop the QVD from being loaded optimised. Loading from a QVD is quick anyway, but the optimised load dumps the entire file straight into RAM (as far as i know).

There are some posts / blogs on the community about the rules to allow an optimised load, i'll post a link if i find them.

Re: Optimized .QVD Confusion

peter_turner — Fri, 08 Nov 2013 15:09:02 GMT

There is a great post by Steve Dark which details information about the optimised QVD loading here

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Re: Optimized .QVD Confusion

Fri, 08 Nov 2013 15:12:18 GMT

I think I am starting to understand more clearly. When creating the .QVD, you can have as many transformations, operations, or conditions as you want. But then when you absorb the .QVD into a .QVW you have to watch what transformations, operations, or conditions you are using for the load to continue to be optimized. Is this correct?

Thank you again for sharing your knowledge!

-Chris

Re: Optimized .QVD Confusion

stevedark — Fri, 08 Nov 2013 16:28:32 GMT

HI Chris,

You may want to take a look at my blog post on the subject:

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Steve

Re: Optimized .QVD Confusion

mbm — Fri, 08 Nov 2013 16:35:23 GMT

Chris,

Here is a good article explaining the ways to ensure your QVD loads are optimized from Steve Dark:

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Re: Optimized .QVD Confusion

peter_turner — Fri, 08 Nov 2013 17:04:58 GMT

Hi Chris,

Looks like Steve's post is very popular!

Yes your understanding is correct, so its best to try and get as much of the work done before you store to QVD.

Also we've found that loading an optimised QVD is faster than a resident table load.

Re: Optimized .QVD Confusion

userid128223 — Sat, 25 Jan 2014 12:51:52 GMT

If you need calculations to be done after QVD is loaded as in in optimized fashion. How do you handle that. In the article it says do calculations and other modification in qvd release stage vs reading stage. can some please explain this in a simple code.

Re: Optimized .QVD Confusion

stevedark — Sat, 25 Jan 2014 15:07:41 GMT

Hi,

You simply can't do calculations on the fields coming out of a QVD without making the load non-optimized. If the load is quick enough anyway then don't worry about it too much. On small tables it is not a problem. If you have a significant number of rows of data in your QVD you will want to ensure that calculations are done when you extract the QVD rather than read it.

So to give you some code, you may want to work out a duration between two dates, like this:

Date(EndTime - StartTime, 'hh:mm:ss') as Duration,

Doing this when loading from a SQL table will make zero difference how long it takes to load. Doing it when loading from QVD could make it up to 100 times slower.

Hope that helps,

Steve

Re: Optimized .QVD Confusion

userid128223 — Mon, 27 Jan 2014 04:09:37 GMT

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

Re: Optimized .QVD Confusion

stevedark — Mon, 27 Jan 2014 09:23:23 GMT

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

Re: Optimized .QVD Confusion

Fri, 05 Dec 2014 21:51:19 GMT

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.

Re: Optimized .QVD Confusion

stevedark — Mon, 08 Dec 2014 20:48:31 GMT

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