topic Re: Processing of WHERE clause in QlikView

Processing of WHERE clause

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello everybody!

I have a conceptual question considering the WHERE-clause in load script. How does Qlik process this statement?

Simple example:

Load *

Resident Table1

Where 1 = 1

and Year(Date) = 2016

and Activity = 1

;

How does Qlik process this statement?

I see 2 possible approaches.

1. a) take Table1

b) take all the rows in Table1 with year = 2016 (let us call it subset1)

c) take all the rows in subset1 with Activity = 1

2. a) take Table1

b) check if the row has year = 2016

c) if step b is true, check if the row has activity = 1

d) process next row

I think right understanding of this process can be very useful in performance increasing.

Have a nice day!

Elena

Re: Processing of WHERE clause

MK_QSL — Fri, 13 Jan 2017 07:19:58 GMT

It should take all rows from Table1 where Year(Date) = 2016 and Activity = 1

I think 2 is the right one

Re: Processing of WHERE clause

Anonymous — Fri, 13 Jan 2017 07:22:15 GMT

Thank you for your opinion, Manish!

Have you somehow checked it?

Re: Processing of WHERE clause

MK_QSL — Fri, 13 Jan 2017 07:24:04 GMT

Let me know how to check it...

Re: Processing of WHERE clause

MK_QSL — Fri, 13 Jan 2017 07:30:45 GMT

Where you use Debug, you will see the number of rows fetched.

You will never see that first 1000 lines fetched and now using 2nd where clause 500 lines fetched..

it will give you one only (500 lines fetched) sentences.

So I think the where clause is not creating any subset ..

Re: Processing of WHERE clause

susovan — Fri, 13 Jan 2017 07:46:32 GMT

You can also try this

Simple example:

Load *

Resident Table1

Where match (Year(Date),' 2016') and match (Activity ,'1');

Re: Processing of WHERE clause

Anonymous — Fri, 13 Jan 2017 08:01:11 GMT

I think it is not so correct to say that fetching means subsequent processing of rows. There are some inner mechanisms, that are not presented to you. Like if you have Group- and Where-clauses within one table transformation, you will only have the final number of rows.

Re: Processing of WHERE clause

Anonymous — Fri, 13 Jan 2017 08:04:09 GMT

Susovan,

I think you didn't catch my question. It is more about the performance.

If Qlik checks first condition and make a subset and then checks next condition, then it is better to organize your conditions with necessary resources increasing.

Like first to check Activity (it is usually 1 or 0 | 1 or null()), and then apply functions to get year.

Re: Processing of WHERE clause

marcus_sommer — Fri, 13 Jan 2017 08:18:24 GMT

I don't know how it will be processed and I could imagine a slight different processing outgoing from the second approach from above that both field-values (and in general each condition) will be checked on row-level and only after reading all records will be evaluated which record is valid or not - quite similar to the qlik-logic to calculate a nested -if-loop which also calculate at first each branch within them and afterwards it will be evaluated which condition is true.

This would meant that the processing of a where-clause is a two-step process and worked from this point of view similar to a group/order by statement from I believe that they are also executed as a two-step process - unless they are combined then it will be a multi-step process (from a performance point of view it's often better not to combine these statements else to use separate steps within a load-chain). I hope hic could give some insights to this topic.

- Marcus

Re: Processing of WHERE clause

Anonymous — Fri, 13 Jan 2017 08:27:37 GMT

In my opinion, if Qlik uses first algorithm in some cases we can win a lot.

Assume you have 1 mln rows with activity = 1 and only 5 have year = 2016. I feel make a subset of 5 rows with year = 2016 and check which of them have activity = 1 is easier then check each row on both conditions.

Re: Processing of WHERE clause

marcus_sommer — Fri, 13 Jan 2017 08:46:13 GMT

I don't think that a subset-query would speed up the process and how should qlikview know which of the conditions would return a TRUE to decide the order in which the queries and sub-queries should be executed - maybe simply from left to the right within the statement but then it would be the knowledge/guessing of the user which order would perform better.

- Marcus

Re: Processing of WHERE clause

ahaahaaha — Fri, 13 Jan 2017 13:02:48 GMT

Hi Elena,

Interested in your question from a practical point of view.

Experiment, what code structure in practice it is faster in QlikView from

1) Where YearDate = '2016' And Activity = 1;

2) Consecutive call

Where YearDate = '2016';

Activity = 1;

Create a file QVD.

Table1:

LOAD

RecNo() as ID,

If(Div(RecNo(), 10000)-RecNo()/10000 = 0, 2016, 2015) as YearDate, //create 100 values in 2016, the rest in 2015

If(Div(RecNo(), 10000)-RecNo()/10000 = 0, If(rand()>0.5, 1, 0), 2) as Activity //half of 2016 assign values 0, 1 half, and assign 2 2015

AutoGenerate 50000000;

Store * from Table1 into F:/xyz.qvd;

File with 50 million records containing

Table from QVD of the first full load, and then begin to work with the selection on the basis of 'WHERE'. This allows you to test how the 'WHERE' with the data already loaded in QlikView.

Load the first variant

Table1:

LOAD ID,

YearDate,

Activity

FROM

(qvd);

Let Start = Now();

NoConcatenate

Table2:

LOAD

ID as ID1,

YearDate as YearDate1,

Activity as Activity1

Resident Table1

Where YearDate = '2016' And Activity = 1;

Let Finish = Now();

As can be seen, the load time of the internal table was 17 seconds.

Loading the same data as in the second variant

Table1:

LOAD ID,

YearDate,

Activity

FROM

(qvd);

Let Start = Now();

NoConcatenate

Table3:

LOAD

ID as ID3,

YearDate as YearDate3,

Activity as Activity3

Resident Table1

Where YearDate = '2016';

NoConcatenate

Table2:

LOAD

ID3 as ID2,

YearDate3 as YearDate2,

Activity3 as Activity2

Resident Table3

Where Activity3 = 1;

Let Finish = Now();

As can be seen, the load time of the internal table was 18 seconds. The duration of load increased by 1 second.

Consecutive five downloads are always given difference 1-2 seconds, which is 5-10% load time.

Logically, the real difference is the internal mechanism QlikView should not be more than.

If the load reduction of 5-10% is important, the issue can be studied further. If this does not help, it is necessary to look for another way out.

I would be glad if something helped you.

Regards

Andrey