topic Re: Optimised load and UI performance in charts in QlikView

Optimised load and UI performance in charts

sonikajain — Tue, 24 Apr 2018 12:58:56 GMT

Hi all,

This is basically to understand one of the statements I came across in a qlikview blog.

I know that optimized qvd affects the QVW load/reload time. I read : Optmized QVD will affect UI performance.

So, my question is: Will a chart in a optimized loaded QVW load faster then in a QVW which is non-optimized loaded.

Re: Optimised load and UI performance in charts

olivierrobin — Tue, 24 Apr 2018 13:03:40 GMT

hello

optimize and non-optilize has meaning only for the load phase

once it is loaded in memory, no matter how it was loaded (optimized or not)

Re: Optimised load and UI performance in charts

sunny_talwar — Tue, 24 Apr 2018 13:06:59 GMT

Where exactly did you read this? Would you be able to share a link to this?

Re: Optimised load and UI performance in charts

sonikajain — Tue, 24 Apr 2018 13:51:03 GMT

The excerpt is : Yes, an unoptimized load can affect the runtime performance of your qvw. Unoptimized loads frequently cause numbers to be converted to strings, which generally will perform slower in the qvw.

Re: Optimised load and UI performance in charts

sunny_talwar — Tue, 24 Apr 2018 13:53:57 GMT

Can you share the link please?

Re: Optimised load and UI performance in charts

marcus_sommer — Tue, 24 Apr 2018 13:58:02 GMT

Loading optimized or not will have no impact to the datatypes of numbers or strings - they are loaded like they are unless you does any transforming on it.

- Marcus

Re: Optimised load and UI performance in charts

sunny_talwar — Tue, 24 Apr 2018 14:01:04 GMT

Is this where you got it from?

QVD optimization and hashing | Qlik Community

rwunderlich‌ does this still hold true?

Re: Optimised load and UI performance in charts

sonikajain — Tue, 24 Apr 2018 14:20:33 GMT

yes this is the link : QVD optimization and hashing | Qlik Community

Re: Optimised load and UI performance in charts

marcus_sommer — Tue, 24 Apr 2018 15:04:32 GMT

I must a bit correct my statement from above. In general it's true unless the value isn't really a number else a dual-value and has beside the numeric value also a string-representation which is either directly stored on a field-value level or within the qvd meta-data (if for a field only one format exists the format is stored within the header else each value is directly stored).

In the case of an unoptimized load the formatting is applied. If the field is a dual-field and has more as one formatting than will the formatting also by an optimized load considered.

Today a number will be stored within a qvd and within a qvw handled with 8 bytes and a dual-date with 20 bytes. You could check this with a small routine like this:

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 1000000;

store t into t.qvd (qvd);

drop tables t;

load * from t.qvd (qvd)

//where year(Date) > 2000

;

by looking on the filesizes within your storage and by looking into the mem-files from the qvw and the xml qvd-header like here:

More background to this could you find here: ByteSizes from Values in QlikView

- Marcus

Re: Optimised load and UI performance in charts

rwunderlich — Thu, 26 Apr 2018 20:47:13 GMT

Still mostly true. Although I would edit my claima bit. It sounds like a I'm saying string operations would be used on the field -- which I may have believed at the time -- thinking they were only strings and not duals, which they are (duals). So the impact of conversion caused by an un-optimized load is an increased memory requirement, which can have a small impact on performance.

You can find these converted fields and measure the memory impact using the Recommendations sheet of Document Analyzer. Some people I know are obsessive about it ("no un-optimized loads!"). I don't worry about it too much but fix the big ones to recover the memory.

-Rob

Re: Optimised load and UI performance in charts

sunny_talwar — Thu, 26 Apr 2018 21:03:38 GMT

Thanks Rob!!

Can you explain this a little more?

So the impact of conversion caused by an un-optimized load is an increased memory requirement, which can have a small impact on performance.

What is converting here? Sorry, if this was straight forward.

Re: Optimised load and UI performance in charts

marcus_sommer — Fri, 27 Apr 2018 07:22:21 GMT

Hi Sunny,

here a short calculation-example:

5 years * 365 days = 1,825 days * 8 bytes = 14,600 bytes

5 years * 365 days = 1,825 days * 20 bytes = 36,500 bytes

The difference between both are 21,900 bytes because it happens only against the distinct fieldvalues stored within the symbol-tables (and the data-tables are just connected through a bit-stuffed pointer). Therefore by many applications you won't notice a performance impact between them because the small ones are fast anyway and by the big ones those few bytes more are negligible - either in running/storing-times in script as well as in loading/calculation-times in UI.

If it's not a date else a timestamp with approximately 5,000 distinct times on each day it looked different:

5 years * 365 days = 1,825 days * 5000 timestamps * 8 bytes = 73,000,000 bytes

5 years * 365 days = 1,825 days * 5000 timestamps * 42 bytes = 383,250,000 bytes

Now the difference would be approximately 210 MB and quite noticeable in each way - and this alone from the store of the symbol-tables. But the pointer would also increase and therefore this should be avoided by splitting dates and times in searate fields - both is very well explained here: https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/11/the-importance-of-being-distinct

This meant to focus on performance-aspects by the big fields/tables and loadings will ensure that the overall performance is sufficient and all the small improvements could be made if there is a bit of unoccupied time (what usually is seldom). I assume this is what Rob had meant …

- Marcus

Re: Optimised load and UI performance in charts

sunny_talwar — Fri, 27 Apr 2018 08:57:14 GMT

Marcus -

Thanks for going over this with an example. It all makes sense except one important thing... how did you come up with these numbers 8 bytes vs 20 bytes when dates and 8 bytes vs 42 bytes when it is timestamp? What determines the size (bytes) here is sort of I am unable to understand completely.

Once again thank you for going over the example, it definitely help me understand the impact....

Best,

Sunny

Re: Optimised load and UI performance in charts

marcus_sommer — Fri, 27 Apr 2018 09:07:30 GMT

Hi Sunny,

it's from my playing here: ByteSizes from Values in QlikView and was here just an assumed size and could in dependence with the choosen format differ - in lesser bytes using a "standard" time-format and even more by using formattings which include the full day- and monthsnames:

- Marcus

Re: Optimised load and UI performance in charts

sunny_talwar — Fri, 27 Apr 2018 11:24:45 GMT

Now this makes a lot more sense.... so, I understand two things from this

1) Date/TimeStamp dual take more space then Date/TimeStamp string? Is this right?

2) Because Rob mentioned this

So the impact of conversion caused by an un-optimized load is an increased memory requirement, which can have a small impact on performance.

it seems that an optimized load will load the Date/TimeStamp as string because it takes less space (better performance) vs. an un-optimized load which will load it as Date/TimeStamp dual. But isn't that a problem that an optimized load will load Date/TimeStamp as string? Or am I missing something here.

Thanks for your continued help Marcus, now that we are talking about this, I am really curious to know what is going on.

Best,

Sunny

Re: Optimised load and UI performance in charts

marcus_sommer — Fri, 27 Apr 2018 14:29:46 GMT

The first is right - a timestamp-string would need less space as a dual-timestamp because the dual-value needs to store the numeric part too and probably also a flag to mark the value as dual.

An optimized load like an unoptimized load will keep a date/timestamp (it's either already directly stored or flagged within the meta-data) as it was. Only the unoptimized load applied a formatting on field-value level if the field is flagged as date/timestamp and has a formatting - for this take a look on my screenshot above for <Type> and <Fmt>.

- Marcus

Re: Optimised load and UI performance in charts

rwunderlich — Mon, 30 Apr 2018 18:36:10 GMT