topic Re: LOAD times in QlikView

LOAD times

datanibbler — Tue, 06 Dec 2016 07:01:41 GMT

Hi,

I have a question rgd. LOAD times for one specific table:

- The table is very big, but on one field it can be split: One part (only one value in that field) has about 5% of all records, the other part (all other values in that field) has about 95% of records.

- There is nów only one filter splitting the table in two: >> TRANCODE = 'SPRECEIVE' << and >> TRANCODE <> 'SPRECEIVE' <<

<=> The first part (LOADed from a qvd file) takes much longer to load than the second one.

These are the two loads: FIRST LOAD:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Trans_Dtl_Rec:
LOAD
ITEM_NUMBER as Rec.ITEM_NUMBER,
TRAN_DATE_TD as Rec.TRAN_DATE_TD,
TRAN_DATE_Time as Rec.TRAN_DATE_Time,
TRAN_CODE as Rec.TRAN_CODE,
QUANTITY as Rec.QUANTITY,
BIN as Rec.BIN,
TRACKING_NUMBER as Rec.TRACKING_NUMBER,
CONTROL_NUMBER as Rec.CONTROL_NUMBER,
SERIAL_LOT_NUMBER as Rec.SERIAL_LOT_NUMBER
FROM $(v_Pfad_TransDtl) (qvd)
WHERE TRAN_CODE = 'SPRECEIVE';

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

SECOND LOAD

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Trans_Dtl_aside:
LOAD
ITEM_NUMBER as Aside.ITEM_NUMBER,
TRAN_DATE_TD as Aside.TRAN_DATE_TD,
TRAN_DATE_Time as Aside.TRAN_DATE_Time,
TRAN_CODE as Aside.TRAN_CODE,
QUANTITY as Aside.QUANTITY,
BIN as Aside.BIN,
TRACKING_NUMBER as Aside.TRACKING_NUMBER,
CONTROL_NUMBER as Aside.CONTROL_NUMBER,
SERIAL_LOT_NUMBER as Aside.SERIAL_LOT_NUMBER
FROM $(v_Pfad_TransDtl) (qvd)
WHERE TRAN_CODE <> 'SPRECEIVE';

=> Is there any way I can speed that up?

Thanks a lot!

Best regards,

DataNibbler

Re: LOAD times

marcus_sommer — Tue, 06 Dec 2016 07:34:07 GMT

Hi DataNibbler,

at first I would check if it's really really needed to split this into two tables - of course you will have a reason but there might be other ways to solve this task.

If you want to speed up a qvd-load you must be to ensure that the load runs optimized. You could reach this by using a where exists() statement. Try something like this:

Dummy:

Load 'SPRECEIVE' as TRAN_CODE autogenerate 1;

Trans_Dtl_aside:

LOAD
ITEM_NUMBER as Aside.ITEM_NUMBER,
TRAN_DATE_TD as Aside.TRAN_DATE_TD,
TRAN_DATE_Time as Aside.TRAN_DATE_Time,
TRAN_CODE as Aside.TRAN_CODE,
QUANTITY as Aside.QUANTITY,
BIN as Aside.BIN,
TRACKING_NUMBER as Aside.TRACKING_NUMBER,
CONTROL_NUMBER as Aside.CONTROL_NUMBER,
SERIAL_LOT_NUMBER as Aside.SERIAL_LOT_NUMBER
FROM $(v_Pfad_TransDtl) (qvd)

WHERE not exists(TRAN_CODE)';

drop table Dummy;

- Marcus

Re: LOAD times

ganeshsvm — Tue, 06 Dec 2016 07:35:34 GMT

Try to load using Where Exist().

Like this,

TEMP;

Load * Inline [

TRAN_CODE

SPRECEIVE

];

TRANS_DTL_REC:

LOAD

......

FROM .....(QVD)

WHERE Exists(TRAN_CODE);

TRANS_DTL_REC:

LOAD

......

FROM .....(QVD)

WHERE Not Exists(TRAN_CODE);

Drop table TEMP;

This will save some time.

Re: LOAD times

datanibbler — Tue, 06 Dec 2016 08:36:53 GMT

Hi,

thanks a lot! I will try.

Yes, the splitting of the table does have a reason - I need the respective parts several times during the script execution, so to make all these LOADs RESIDENT, I load the table beforehand, and since in some places I need only the smaller part, in others I need only the bigger part, it makes sense as both parts are smaller to parse than the total ...

This still does not explain why the smaller part takes much longer in the loading than the big one - but never mind. If I can speed the whole thing up, all the better.

Now I have introduced a partial_reload_scheme, in case the report is used several times one after another, so that these tables are just left standing instead of being reloaded every time.

What I'd like to do next is, building on top of this, to make the script decide itself when it makes sense to reload that big table - once an hour, when it has been updated from the database - and otherwise, it should always perform a partial reload - or I could do it otherwise - but I will make that a new thread in order to not confuse things here.

Best regards,

DataNibbler

Re: LOAD times

datanibbler — Tue, 06 Dec 2016 08:52:16 GMT

Hi,

the LOADs still do not run in optimized mode and I cannot spot why:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Temp:
LOAD 'SPRECEIVE' as TranCode2look4 AutoGenerate 1;

Trans_Dtl_Rec:
LOAD
ITEM_NUMBER as Rec.ITEM_NUMBER,
TRAN_DATE_TD as Rec.TRAN_DATE_TD,
TRAN_DATE_Time as Rec.TRAN_DATE_Time,
TRAN_CODE as Rec.TRAN_CODE,
QUANTITY as Rec.QUANTITY,
BIN as Rec.BIN,
TRACKING_NUMBER as Rec.TRACKING_NUMBER,
CONTROL_NUMBER as Rec.CONTROL_NUMBER,
SERIAL_LOT_NUMBER as Rec.SERIAL_LOT_NUMBER
FROM $(v_Pfad_TransDtl) (qvd)
WHERE EXISTS(TranCode2look4, TRAN_CODE);

Re: LOAD times

marcus_sommer — Tue, 06 Dec 2016 09:31:37 GMT

Hi DataNibbler,

only the single-parameter version of the exists(TRAN_CODE) statement will be optimized. As far as you put a second parameter into it you forced qlik to process this load instead of transferring it directly into the RAM.

Although the approach of using where exists() worked very well you could be forced to adjust your load-approaches a bit to be able to use the single-parameter version in cases where this field is already loaded within previous loadings.

One approach is to create a temp-field for it within the load which creates the qvd and another would be to rename the fields like:

rename field TRAN_CODE into TRAN_CODE_TEMP;

Temp:
LOAD 'SPRECEIVE' as TRAN_CODE AutoGenerate 1;

Trans_Dtl_Rec:
LOAD
ITEM_NUMBER as Rec.ITEM_NUMBER,
TRAN_DATE_TD as Rec.TRAN_DATE_TD,
TRAN_DATE_Time as Rec.TRAN_DATE_Time,
TRAN_CODE as Rec.TRAN_CODE,
QUANTITY as Rec.QUANTITY,
BIN as Rec.BIN,
TRACKING_NUMBER as Rec.TRACKING_NUMBER,
CONTROL_NUMBER as Rec.CONTROL_NUMBER,
SERIAL_LOT_NUMBER as Rec.SERIAL_LOT_NUMBER
FROM $(v_Pfad_TransDtl) (qvd)
WHERE EXISTS(TRAN_CODE);

rename field TRAN_CODE_TEMP into TRAN_CODE;

- Marcus

Re: LOAD times

datanibbler — Tue, 06 Dec 2016 09:37:48 GMT

Hi Marcus,

I cannot quite follow the execution_order of your code - where does this RENAME statement come in?

Re: LOAD times

marcus_sommer — Tue, 06 Dec 2016 09:55:12 GMT

Hi DataNibbler,

if your field is already loaded and maybe therefore not be usable for the where-exists-logic you would rename it directly before you want to load your optimized qvd's and afterwards you might want/need to rename it again - it will be depending on your datamodel.

Of course this kind of scripting adds some more overhead and complexity but it's the price you need to pay to get optimized loadings.

Here are various quite explanatory links included to the exists-topic which will help you to understand how exists worked and how it could be implemented: Advanced topics for creating a qlik datamodel.

- Marcus

Re: LOAD times

datanibbler — Wed, 07 Dec 2016 06:55:16 GMT

Thanks Marcus!

I will have a look.

Re: LOAD times

datanibbler — Wed, 07 Dec 2016 08:58:58 GMT

Hi Marcus,

I think I have read all of the articles on the issue. I know what the issue is - but I still cannot imagine how to avoid it. The confusing thing about your post (your example) is that those RENAME commands come as the very first and last statements, around everything else, not within the LOAD, so I don't know what they are supposed to do - although I think I remember I have come across this issue before ...

Sorry. Can you give me one more hint?

Thanks a lot!

Best regards,

DataNibbler

Re: LOAD times

marcus_sommer — Wed, 07 Dec 2016 10:05:58 GMT

Hi DataNibbler,

the logic behind where exists() is to compare the fieldvalues from the load-table against the list of values which already exists within this field - an this happens for each single record within the load and each new fieldvalue will be added to this field while the runtime (this happens in general unless there is a condition against it).

This meant you need to prepare this field with the appropriate fieldvalues (which you want to filter in or out) before you could it within the load-statement. For this reason we created the table "Dummy" above with the needed fieldvalue and fieldname - but if this field already exists it could contain fieldvalues which don't suit to your requirement. In this cases you could (often) use exists with a second parameter by loosing the optimized load or to create an additional temp-field within the qvd or like in my suggesting to rename the field.

In each case this will add some overhead and complexity and if your script is already quite complex it's not always very easy to implement it. Perhaps it would be useful if you split your load-approach into several applications respectively more layer within data-arcitecture to simplify your loadings.

- Marcus

Re: LOAD times

datanibbler — Wed, 07 Dec 2016 10:38:08 GMT

Okay Marcus,

thanks again! I still haven't fully understood - mainly because in your suggestion you use the RENAME statement before the field to rename is even loaded - but never mind: You say it would add complexity, and I am as a rule trying to avoid making things too complex so anyone else could, without being an expert, with reasonable effort, understand and maintain my applications. So I think, I will still try to understand this for knowledge's sake, but for the users, I will go for the inbetween_way of using EXISTS with two parameters and implement the partial_reload scheme instead.

That inbetween_way is still faster than loading normally, I think - as of now, that is, because we "restarted" that database in June, there is not so much in there, and I had to take out the date_filter to enable this - it might become slower again going forward, but I don't mind right now.

I just noticed another issue with that - somewhere, a copy of a table or something is generated, generating a large synthetic key. With just 10 records, that's not really an issue, I'm just testing what happens if I do several partial reloads in a row ...

Re: LOAD times

datanibbler — Wed, 07 Dec 2016 10:43:52 GMT

New issue:

It's pretty clear what's happening: One table is now loaded within a loop - I use the REPLACE keyword in the first iteration and ADD afterwards - there are 10 iterations in the instance I'm testing.

=> After that loop is closed, there comes a LEFT JOIN from another table adding 3 more fields to the table from the loop - and at that point, a copy of that table seems to be generated, resulting in an enormous synthetic key.

Seemingly, even when there are several partial LOADs in a row, 1 copy of that table is all I get, that's not too bad - but still I don't quite understand it.

Oh - that happened before I intróduced the use of two different keywords for this loop, so it is not because of that ...

Do you have any idea what could cause this?

I will now go on lunch_break and then start testing this in the Debugger ...

Thanks a lot!

Best regards,

DataNibbler

Re: LOAD times

marcus_sommer — Wed, 07 Dec 2016 11:05:01 GMT

It's quite difficult to give you an advice than your script is quite large and complex - maybe too complex and it would be useful to split it and maybe removing your partial-load approach and trying to speed up your load-times with other methods.

Personally I don't use partial loadings and it would be my last step in improving the load-times if all other methods creating the datamodel and forcing of optimized loads, incremental loadings, modifying binary loads and so on aren't fast enough.

- Marcus

Re: LOAD times

datanibbler — Wed, 07 Dec 2016 12:38:57 GMT

Hi Marcus,

maybe you're right.

I will keep the script where I have already implemented the partial_reload - it works as such, so it would be a shame to just bin it, and maybe redo it lateron - but I will try to optimize whatever else I can before I use that as it does bring with it the possibility for the user to do it wrong, which I always try to avoid ... since the tables used for that report are one and all only reloaded from the database once an hour, a partial reload doesn't seem a bad idea - but maybe I can create one "inbetween" qvw (between our main loading_scripts running once an hour and this app) that would load all the necessary tables so that can be loaded BINARY ...

There are some possibilities that I can think of, remoing JOINs by moving them "further up the road" into the first tier of our installation and store the already-joined tables as qvd and such.

To return to where we started, kind of - an EXISTS()-clause is always faster than a simple filter, though not as fast as an optimized LOAD, is that about right?

Re: LOAD times

marcus_sommer — Wed, 07 Dec 2016 13:34:29 GMT

Hi DataNibbler,

nothing is faster as an optimized load - but I haven't tested yet if there are significantly differences by not optimized loadings on how the filtering is applied. I would assume the following order:

- where exists(Par1, Par2)

- normal where-clause like date >= ....

- joins

whereby it will depend on the concrete scenario.

- Marcus

Re: LOAD times

datanibbler — Wed, 07 Dec 2016 13:49:30 GMT

Hi Marcus,

that's what I thought, and it confirms what I assumed: An EXISTS clause is generally always faster than a specific WHERE filter.

I will try to load all necessary tables in an inbetween_app running "in the wake" of our primary data_loading script (I would need two of those, one for each database we have) and load that BINARY in the actual report.

Plus, I will try to replace the filters by EXISTS clauses in that report wherever possible.

Best regards,

DataNibbler

P.S.: Still - I don't understand that about the optimized LOAD with EXISTS (one parameter) - why did you place that RENAME command before all LOADs in your suggestion - what does it do there, to what end did you do that? I do understand why it would be necessary to rename that field - but that would have to happen immediately after loading every single record, no?

Re: LOAD times

marcus_sommer — Wed, 07 Dec 2016 14:30:24 GMT

Hi DataNibbler,

your field within the load will be TRAN_CODE and this will be compared against an already existing field of TRAN_CODE and which needs to be checked/created/prepared in beforehand. Your renaming with an alias to Rec.TRAN_CODE isn't important here and you couldn't use these renamed field anywhere within the current load-statement as reference - it does exists only after the load is finished.

- Marcus

Re: LOAD times

datanibbler — Wed, 07 Dec 2016 14:53:06 GMT

I still don't get you 😉 Sorry.

So you think my renaming as Rec.Tran_Code would already be enough since that alias_name only exists after the entire LOAD is finished?

Then, would I not have to load the field AS Rec.Tran_Code in that Dummy_table?

I still cannot fit in your renaming into TRAN_CODE_TEMP - because it doesn't figure anywhere in the LOADs until you undo that after all the LOADs are done ...

Re: LOAD times

marcus_sommer — Wed, 07 Dec 2016 15:04:41 GMT

Hi DataNibbler,

no, I didn't mean that. We should now forget Rec.Tran_Code because it's not involved. The renaming before this qvd-load from Tran_Code to Tran_Code_Temp is only necessary if this field already exsists and don't contained the proper values. If Tran_Code don't exists before you could remove the rename-statement - it should be only an example of methods which could be useful to prepare the fields/data to be able to perform the load in an optimized load.

- Marcus