topic Re: Non Optimized QVD Load vs Resident Load - qlikview in QlikView

Non Optimized QVD Load vs Resident Load - qlikview

Thu, 22 Oct 2015 17:07:35 GMT

Hello All

I have a Qlikview project where a Table A QVD contains about 1.5 million rows. i have already loaded this QVD into the memory.

I want to load another table Table B which is a subset of Table A using a where clause.

e.g.

Table B:

Load

AID as BID,

.....

resident load Table A

where Purchase Year = Last Purchase Year

In such a case is an Non Optimized QVD Load (because of where clause) better or a Resident Load?

Any help from your experience appreciated

Re: Non Optimized QVD Load vs Resident Load - qlikview

Anonymous — Thu, 22 Oct 2015 17:27:23 GMT

If you can, I would put a flag on that table saying 1 as _LastYearFlag and use that to denote last year's data. That way you don't have to reload and duplicate data.

If you must, doing a resident load would be better performance wise.

Re: Non Optimized QVD Load vs Resident Load - qlikview

maxgro — Thu, 22 Oct 2015 18:20:02 GMT

In this case I think I'll use a resident load

but

I would not worry about the diffference between resident and not optimized for a 1,5 million file

Re: Non Optimized QVD Load vs Resident Load - qlikview

Thu, 22 Oct 2015 18:22:47 GMT

the actual QVD is about 1.7 gig in size. 200+ columns

Hence my question.

Re: Non Optimized QVD Load vs Resident Load - qlikview

Anonymous — Thu, 22 Oct 2015 18:29:06 GMT

It will not matter a much. Rather than loading a qvd again take a resident load. Just to understand why you want to reload the data again. You can play with the loaded data for any year..

Imran K

Re: Non Optimized QVD Load vs Resident Load - qlikview

Anonymous — Thu, 22 Oct 2015 18:30:06 GMT

Depending on the number of columns in the table... if the table has 100 columns, this could be a different story.

Re: Non Optimized QVD Load vs Resident Load - qlikview

Anonymous — Thu, 22 Oct 2015 18:52:46 GMT

I'd recommend a flag for current year then. If not, then a resident load would be best.

Re: Non Optimized QVD Load vs Resident Load - qlikview

maxgro — Thu, 22 Oct 2015 18:53:56 GMT

this is the result of a small test, you find the script at the end

optimized, non optimized, resident, the elapsed is 1 or 2 second

result

t << AUTOGENERATE(1000000) 1.999.146 lines fetched

***** read qvd optimized 22/10/2015 20:51:30

t << T (qvd optimized) 1.999.146 lines fetched

22/10/2015 20:51:31

***** read resident 22/10/2015 20:51:31

tr << t 333.454 lines fetched

22/10/2015 20:51:32

***** read qvd non optimized 22/10/2015 20:51:32

tq << T 333.454 lines fetched

22/10/2015 20:51:33

script

Load

TransLineID,

TransID,

mod(TransID,26)+1 as Num,

Pick(Ceil(3*Rand1),'A','B','C') as Dim1,

Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,

Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,

Round(1000*Rand()*Rand()*Rand1) as Expression1,

Round( 10*Rand()*Rand()*Rand1) as Expression2,

Round(Rand()*Rand1,0.00001) as Expression3;

Load

Rand() as Rand1,

IterNo() as TransLineID,

RecNo() as TransID

Autogenerate 1000000

While Rand()<=0.5 or IterNo()=1;

store t into t.qvd (qvd);

drop table t;

// optimized

let t=now(); trace ***** read qvd optimized $(t);

t: load * from t.qvd (qvd);

let t=now(); trace $(t);

// resident

let t=now(); trace ***** read resident $(t);

tr: load *, 1 Resident t where Dim2 = 'a';

let t=now(); trace $(t);

drop table tr, t;

// qvd non optimized

let t=now(); trace ***** read qvd non optimized $(t);

tq: load *, 1 from t.qvd (qvd) where Dim2 = 'a';

let t=now(); trace $(t);

drop table tq;

Re: Non Optimized QVD Load vs Resident Load - qlikview

Thu, 22 Oct 2015 19:41:58 GMT

thanks guys.

i cannot use a flag for this instance.

my understanding is also the same i.e. use resident load.

the confusion comes from not finding any solid reference materials anywhere.

only way of confirming this is for me is to run this on the pre production server and check; which is a pain

Re: Non Optimized QVD Load vs Resident Load - qlikview

JonasValleskog — Thu, 22 Oct 2015 21:21:52 GMT

The Optimized QVD load is faster as long as your I/O bandwidth is good. My machine has a solid state drive, so unlikely a fair test machine to compare with most servers out there today, but never the less, here are my results when running the well written script by maxgro with a few minor improvements.

Notes:

It is possible to achieve an OPTIMISED qvd load even with a filter condition, if the condition can be satisfied by a WHERE EXISTS() clause in where the input field has the same name as the field you are checking against. This can be used to your advantage here and without it, the test is not comparing like-for-like when considering the time lapse for an optimised QVD load. See below.

A tweaked script gives this output:

t << AUTOGENERATE(10000000) 10,000,000 lines fetched

***** read resident 22/10/2015 22:08:39

tr << t 1,665,914 lines fetched

00:00:04

Dim2 << INLF7C 1 lines fetched

***** read qvd optimized 22/10/2015 22:08:44

t << t (qvd optimized) 1,665,914 lines fetched

00:00:01

***** read qvd non optimized 22/10/2015 22:08:45

tq << t 1,665,914 lines fetched

00:00:05

Modified script

Load

TransLineID,

TransID,

mod(TransID,26)+1 as Num,

Pick(Ceil(3*Rand1),'A','B','C') as Dim1,

Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,

Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,

Round(1000*Rand()*Rand()*Rand1) as Expression1,

Round( 10*Rand()*Rand()*Rand1) as Expression2,

Round(Rand()*Rand1,0.00001) as Expression3;

Load

Rand() as Rand1,

IterNo() as TransLineID,

RecNo() as TransID

Autogenerate 10000000

;

store t into t.qvd (qvd);

// resident

let t=now(); trace ***** read resident $(t);

tr: load *, 1 Resident t where Dim2 = 'a';

let t=time(now()-t); trace $(t);

drop table tr,t;

Dim2:

LOAD * INLINE [

Dim2

];

// optimized

let t=now(); trace ***** read qvd optimized $(t);

t: load * from t.qvd (qvd)

WHERE EXISTS(Dim2);

let t=time(now()-t); trace $(t);

DROP TABLE t;

// qvd non optimized

let t=now(); trace ***** read qvd non optimized $(t);

tq: load *, 1 from t.qvd (qvd) where Dim2 = 'a';

let t=time(now()-t);; trace $(t);

drop table tq;

Re: Non Optimized QVD Load vs Resident Load - qlikview

marcus_malinow — Fri, 23 Oct 2015 08:12:43 GMT

Hi Dilip,

there is a ton of information on the subject of optimised loads and resident loads in the forum so I'm not sure why you've raised this question. Fundamentally, whether a load from QVD, or a Resident load will be quicker depends on your specific circumstances. This includes things like your environment, whether you're dealing with lots of text fields or not, how unique your data is, etc, etc, etc.

First off, are you dealing with 1.5 million or 1.7 billion rows? If it's 1.7 billion, are these all in a single QVD? If so, have you not thought about segmenting this file?

Incidentally, a WHERE EXISTS clause WILL be optimised. You could try something like:

TempFilter:

LOAD * INLINE [

FilterYear

2015];

Table B:

Load

FROM [Your QVD]

WHERE EXISTS(FilterYear, Year);

What will break optimisation though are things like calculated fields, renamed fields, and pretty much any manipulation in the initial load.

Marcus

Re: Non Optimized QVD Load vs Resident Load - qlikview

maxgro — Fri, 23 Oct 2015 08:17:50 GMT

same test, about the same result (SSD disk)

it seems the best one is qvd optimized and if you can't do an optimized load, resident and not optimized qvd are similar

t << AUTOGENERATE(10000000) 10.000.000 lines fetched

***** read resident 23/10/2015 10:12:56

tr << t 1.665.621 lines fetched

00:00:06

Dim2 << INLB829 1 lines fetched

***** read qvd optimized 23/10/2015 10:13:02

t << T (qvd optimized) 1.665.621 lines fetched

00:00:02

***** read qvd non optimized 23/10/2015 10:13:04

tq << T 1.665.621 lines fetched

00:00:06

Re: Non Optimized QVD Load vs Resident Load - qlikview

jonathandienst — Fri, 23 Oct 2015 08:41:34 GMT

@marcus

My understanding is that your example would not be optimised. Only the single parameter Exists will keep the load optimised. Like

Table B:

Load

FROM [Your QVD]

WHERE EXISTS(FilterYear);

Or has this changed in a recent SR?

Re: Non Optimized QVD Load vs Resident Load - qlikview

marcus_malinow — Fri, 23 Oct 2015 08:50:46 GMT

Aah my bad, you're quite right.

Re: Non Optimized QVD Load vs Resident Load - qlikview

JonasValleskog — Fri, 23 Oct 2015 09:29:08 GMT

Dilip R

I suggest you confirm one of the many responses saying pretty much the same thing as correct so we can close this thread. It's not particularly helpful to have lots of versions of almost the same response, I guess that's just the nature of forums - concluding with a correct answer should help future readers find what is most relevant in an answered thread.

Ps. It does not seem possible to 'mention' your name for some reason, just so you know. ( moderator‌ care to comment on how to 'mention' Dilip R? Or is it privacy settings maybe preventing it?) Ds.

Cheers

Jonas

Re: Non Optimized QVD Load vs Resident Load - qlikview

Fri, 23 Oct 2015 14:55:40 GMT

If i can do optimized load i dont doubt that would be better.

the where clause has to compare 2 dates columns within the table hence results in non optimized load. i cant find any documentation comparing non optimized qvd load and resident load.

judging from the answers and the size of the date handled within the application and qvd residing on a shared drive resident load seem to be the way to go.

once i get a chance to test in pre production i will update the thread

Re: Non Optimized QVD Load vs Resident Load - qlikview

JonasValleskog — Sat, 24 Oct 2015 06:51:49 GMT

‌... Just a note as you mentioned you had a two field criteria:

it may sometimes be faster to do an optimised load of all data and then a RIGHT KEEP to reduce down the loaded data to what you need if you can't easily design a single field WHERE EXISTS (<field>) clause.

tmp:

RIGHT KEEP(bigTable)

LOAD * INLINE [

Field

];

DROP TABLE tmp;

Also, remember that you can construct a surrogate unique field from two independent field. FIELD1 & '|' & FIELD2 as FIELD3

Either method should allow you to benefit from an optimised QVD load

Cheers

Jonas