topic Re: Detail question on STORE in QlikView

Detail question on STORE

datanibbler — Fri, 27 Apr 2018 07:45:02 GMT

Hi,

I would like to know: In the apps here, there is quite often a STORE in the form of

>> STORE * FROM ... INTO ... <<

This has obviously been working fine, I just never used the FROM in this statement - I always just used

>> STORE ... INTO ... <<

My question is, does this have any effect? Does this change anything about the statement? What about the speed?

Thanks a lot!

Best regards,

DataNibbler

Re: Detail question on STORE

MK_QSL — Fri, 27 Apr 2018 07:54:54 GMT

* or FieldName is used when you don't want the full table into QVD or flat files.

Say you have below table.

Data:

Load Country, Year, Sales From ....

You want to store only Country and Sales then you can use as below.

Store Country, Year From Data into Data.qvd(QVD);

Below two lines will give you same output.

Store * From Data into Data.qvd(QVD);

Store Data into Data.qvd(QVD);

I never tried but the above two should give same performance.

Re: Detail question on STORE

keerthika — Fri, 27 Apr 2018 08:13:00 GMT

Hi,

I think that instead of using Store * From data.qvd using column names takes much faster to execute because in sql select (column names) from data.qvd takes less time to execute the result instead of using *.

Re: Detail question on STORE

datanibbler — Fri, 27 Apr 2018 08:19:42 GMT

Hi Keerthika,

I will try and report back whether it is.

If nothing else, that way would have the advantage that any fields which are later not loaded anymore could eventually be left out of the STORE in the first place.

Re: Detail question on STORE

keerthika — Fri, 27 Apr 2018 08:22:50 GMT

Hi,

Okay . you just try it and response me back.

Re: Detail question on STORE

datanibbler — Fri, 27 Apr 2018 11:06:35 GMT

Well, I had no "clean-room-scenario" for testing, the STORE was via a network connection to a server path, so there might have been some interference, I'll have to test it again eventually.

I tested with a really big table - about 47 mio records, running QlikView on my local laptop and storing to the server.

=> It seems that the variation >> STORE * FROM << is indeed faster than the simple STORE, although I cannot think of a logical explanation, and the >> STORE [list of fields] FROM << (with all fields in for now) is somewhere inbetween.

Re: Detail question on STORE

keerthika — Fri, 27 Apr 2018 11:14:49 GMT

Hi,

Okay. Thanks for reply! Have a nice day!

Re: Detail question on STORE

marcus_sommer — Fri, 27 Apr 2018 14:48:12 GMT

I just did a small testing to the various store-statements and would say that there is no significantely difference between them. The differences between the single store-statements are mainly caused by inaccuracies through the fact that now() didn't return milliseconds else only full seconds and that there are further I/O between the OS and the network/storage and a store might be queued in some way.

But between the network and a local storage is a big difference ...

Here what I did with a table with 2 fields and 10 M records:

t:
load
recno() * 2 as ID,
Date(makedate(2000) + ceil(rand() * 1460), if(mod(recno(), 2)=0, 'DD.MM.YYYY', 'YYYY-MM-DD')) as Date
//floor(makedate(2000) + ceil(rand() * 1460)) as Date
autogenerate 10000000;

let i = rangesum($(i), 1);
let start = num(now() - today(), '#.########', '.', ',');
store t into (qvd);
StoreTimes:
load time($(start)) as Start, time(now() - today()) as End, time(now() - timestamp($(start))) as Result,
'store table into' as Type, $(i) as Iteration, 'SSD' as Storage, 'overwritten' as QVD autogenerate 1;

let start = num(now() - today(), '#.########', '.', ',');
store * from t into (qvd);
StoreTimes:
load time($(start)) as Start, time(now() - today()) as End, time(now() - timestamp($(start))) as Result,
'store * from table into' as Type, $(i) as Iteration, 'SSD' as Storage, 'overwritten' as QVD autogenerate 1;

let start = num(now() - today(), '#.########', '.', ',');
store ID, Date from t into (qvd);
StoreTimes: load time($(start)) as Start, time(now() - today()) as End, time(now() - timestamp($(start))) as Result,
'store fields from table into' as Type, $(i) as Iteration, 'SSD' as Storage, 'overwritten' as QVD autogenerate 1;

if filetime('StoreTimes.qvd') > 0 then
load * from StoreTimes.qvd (qvd);
end if

store StoreTimes into StoreTimes.qvd (qvd);

drop tables t;

resulting in:

- Marcus

Re: Detail question on STORE

datanibbler — Mon, 30 Apr 2018 06:46:47 GMT

Hi Marcus,

okay, I know that here there is something interfering, so my testing is not perfect, so I'll just take your results as granted. Thanks a lot!

That leads me to some more potential for optimization - I have a multi-tier environment. If, in the "datamodel.qvw", I have a number of fields which are dropped at the end, the first step to optimize would be to not load these in the first place - a second step would be not to store them in the preceding "transform.qvw", no?

Best regards,

DataNibbler

Re: Detail question on STORE

marcus_sommer — Tue, 01 May 2018 16:10:21 GMT

Hi DataNibbler,

yes, the most time would be saved if unneeded data aren't loaded instead of dropping them anywhere afterwards. On the other side it might help to load things on the right time and the right place.

For example I have splitted my orderline-data into 3 tables - data from OrderLineFirst do I need in most of all applications but OrderLineSecond/Third are only needed in a few and from a timing point of view less important applications (and there are fields included which I currently don't use). So are my important data small and fast and the not so important data could run within another time-frame ...

- Marcus

Re: Detail question on STORE

datanibbler — Thu, 03 May 2018 07:01:54 GMT

Hi Marcus,

I hope I understand your point correctly - I think that is all right: The original data from SAP is loaded "outside" all individual reports anyway, so it is still there - the transformation.qvw where I want to remove the unnecessary fields (where I then have to add data from NonSAP-subsidiaries from Excel) is specific for this one report - I go backwards from the datamodel.qvw which is somewhat of a lot of work as there are many fields which are not needed - did I say at some point that I dislike the >> LOAD * << 😉 . Still, it is possible that later transformation steps specific to this report might refer to something from this one. I will check that. This is the first transformation to run in this context, so I will have to search all the others, too.

Best regards,

DataNibbler

Re: Detail question on STORE

marcus_sommer — Thu, 03 May 2018 08:21:04 GMT

Hi DataNibbler,

in addition to my statement from above it might be helpful to use more granular layers within the environment. My OrderTables are for example already the second layer grabbing the transactional data from the first layer (in both happens only a few transformations - they are mostly to distribute the data) and the next layer extracted from them various mapings and aggregations which are then matched within the fourth layer and all the output qvd's from these layers fill the fifth datamodel-layer which only contain a few transformations (a few renames, autonumber and so on) and just match the different qvd's into the datamodels from which the reports are loading binary.

Nearly everything within the layer 1 - 4 are loading the data incremental and slicing the qvd's on a monthly level. This all is of course conceptual and in the developing a bit more expensive as a 2/3-tier architecture (which if it included any incremental methods mostly on te first layer) but afterwards I'm quite flexible to distribute and parallelize the various parts.

- Marcus

Re: Detail question on STORE

datanibbler — Thu, 03 May 2018 11:03:33 GMT

Hi,

well, I won't mess with that too much for now. There are quite a few layers to begin with, and data is, as I understand it, loaded from SAP incrementally - although there is everything in Qlik which poses a bit of a performance-problem on the surface ....

There are the extractors on the 1st layer, drawing data from SAP and in some cases from Excel.

Then come a few global transformations (not report-specific).

Specific for each report, there are several transformation steps for different things, each one using the original qvd's (from the 1st and 2nd layer).

After all the transformation steps, there is a datamodel.qvw for each report, just drawing and combining all the transformed qvd's - the datamodel is finally loaded with a BINARY into the application.

I think some consultant came up with that structure as it was the same in the last company I worked for. I guess it

does have advantages and we can add more transformations if we need some.