topic Re: Performance issue with 'where exists' clause in QlikView

Performance issue with 'where exists' clause

Mon, 26 Jan 2026 18:19:17 GMT

Hi,

I have an issue with the load of really large table. It deals with user to customer relationships and is the preparation for the section access.

First of all, I load a smaller table (about 1000 rows) with users who shall be able to see everything.

Then I load a table that has about 1 billion rows with a "where not exists (user_id)" clause,

Table A:

LOAD

USER_ID,

VIEW_ALL_FLAG

FROM

...

;

Table B:

LOAD

USER_ID,

CUSTOMER_ID

FROM

....

Where not exists(USER_ID, USER_ID)

;

The load takes several hours. How can I redesign the load to take shorter time?

Thanks,

Jens

Re: Performance issue with 'where exists' clause

MayilVahanan — Wed, 17 Feb 2016 08:59:04 GMT

Try like this

MapUserID:

Mapping Load USER_ID, 1 Resident TableA;

Load * where Flag = 1;

Load *, ApplyMap('MapUserID', USER_ID, 0) AS Flag From TableB;

Re: Performance issue with 'where exists' clause

Wed, 17 Feb 2016 09:16:52 GMT

Hi,

that's not the result that I need. Most likely it was not understable without sample data, sorry for that.

So small example:

TableA contains USER_ID 'A', so 'A' shall be allowed to see everything later in the application.

USER_ID VIEW_ALL_FLAG

A Y

The source for TableB contains:

USER_ID CUSTOMER_ID

A 1

A 2

B 1

B 4

B 8

C 3

My final TableB shall contain this:

USER_ID CUSTOMER_ID VIEW_ALL_FLAG

A Y

B 1

B 4

B 8

C 3

So 'A' does not have a direct assignment to a customer as he is a VIEW_ALL user. 'B' and 'C' have direct assignments as they are normal users.

Re: Performance issue with 'where exists' clause

Wed, 17 Feb 2016 09:20:58 GMT

Maybe change

Load * where Flag = 1;

Load * where Flag <> 1;

Re: Performance issue with 'where exists' clause

MayilVahanan — Wed, 17 Feb 2016 10:07:46 GMT

Please Provide a sample data with expected results.

Re: Performance issue with 'where exists' clause

Wed, 17 Feb 2016 10:40:05 GMT

Hi,

please find attached.

Re: Performance issue with 'where exists' clause

marcus_sommer — Wed, 17 Feb 2016 11:07:15 GMT

Unfortunately you didn't show the sources from your loadings. I assume that at least the bigger table will be loaded from a qvd-file and then it is important to perform an optimized load which could be only done with no real transformings within the load and with a where-clause with exists(WithOnlyOneParameter) - in your case you didn't need the second parameter and could remove them.

If this isn't enough or it isn't a qvd you need to consider to implement incremental methods.

To both suggestions you will find here many examples: Advanced topics for creating a qlik datamodel

- Marcus

Re: Performance issue with 'where exists' clause

MayilVahanan — Wed, 17 Feb 2016 11:09:05 GMT

TRy like this

TableA:

LOAD * INLINE [

User, User_key, View_all

A,A, 1

B,B, 1

C,C, 1

D,D, 1

E,E, 1

F,F, 1

G,G, 1

];

MapTableA:

Mapping LOAD User_key, 1 Resident TableA;

TableB:

LOAD * Where Flag = 0;

LOAD *, ApplyMap('MapTableA', User, 0) AS Flag INLINE [

User, Customer

A, 30

A, 44

A, 73

B, 51

B, 73

B, 21

C, 41

C, 100

C, 75

D, 41

D, 37

D, 2

E, 35

E, 7

E, 37

F, 58

F, 40

F, 70

G, 4

G, 15

G, 42

H, 1

H, 14

H, 28

I, 21

I, 61

I, 77

J, 71

J, 83

J, 60

K, 62

K, 39

K, 78

L, 70

L, 42

L, 26

];

Re: Performance issue with 'where exists' clause

Wed, 17 Feb 2016 11:15:31 GMT

Hi Marcus,

you're right, the tables are QVD based. Unfortunately, the big table is a result of a hierarchy command and some inner joins between QVDs, linking from User to Customer with a key field each.

Re: Performance issue with 'where exists' clause

marcus_sommer — Wed, 17 Feb 2016 11:23:26 GMT

Hi Jens,

like above mentioned try it with only one exists-parameter. Further it seems that you tried to extract the user from a fact-table (or have you really 1 billion customers ?) and that loading from a customer table or from a beforehand reduced fact-table could be have benefits.

- Marcus

Re: Performance issue with 'where exists' clause

Wed, 17 Feb 2016 14:14:44 GMT

The approach with only one exists-parameter is still running and taking about twice the time from before. Will need to analze tomorrow where this is coming from.

Re: Performance issue with 'where exists' clause

marcus_sommer — Wed, 17 Feb 2016 14:29:57 GMT

Then take also a look on the RAM consumption within the taskmanager then loading two fields from a qvd in an optimized load should be even by one billion records quite fast - from a local storage maybe in around 15 minutes (on really performant hardware with SSD's as storage even a lot more faster).

- Marcus

Re: Performance issue with 'where exists' clause

Thu, 18 Feb 2016 07:00:08 GMT

RAM and CPU consumption look ok, at about 30 GB out of 196 on the test machine (production 384), CPU at about 4% usage.

I work on input optimization to reduce load time by preparing the data with the purpose that no "where exists" is needed anymore. What I still find strange is a long break the system seems to take. From the log:

16.02.2016 08:41:11: 0145 RESIDENT

16.02.2016 08:41:11: 0146 TERRITORY

16.02.2016 08:41:11: 0147 WHERE NOT

16.02.2016 08:41:11: 0148 EXISTS(USER_KEY_ALL,USER_KEY_TE&'_ALL')

16.02.2016 08:41:11: 5 fields found: USER_KEY_ALL, ACCESS, NTNAME, PARTY_ID, TerritoryId, 1.209.721.637 lines fetched

16.02.2016 12:26:27: 0152 AccessTableParty:

That is the next step. After loading the QVD files with several inner joins, the result is concatenated to an existing (smaller) table.

There are several hours without progress (at least without recognizable progress). Any ideas what this might be?

Thanks again,

Jens

Re: Performance issue with 'where exists' clause

tresB — Thu, 18 Feb 2016 07:12:48 GMT

Potentially it could be a join issue. Try with limited load. Check the data model:

Is there any synthetic keys getting generated?
Does your key field get repeated after join?

Re: Performance issue with 'where exists' clause

Thu, 18 Feb 2016 07:18:37 GMT

What do you mean with your second question?

Re: Performance issue with 'where exists' clause

tresB — Thu, 18 Feb 2016 07:23:13 GMT

Say in your Key field(which you want the join to perform on with another table) has 100 records and after your inner join, it becomes 100+. This could happen, if both the key fields have non-distinct (repeated) values. This is a possible case of generating many unnecessary and incorrect records while joining - that can slow down your reload process.

Re: Performance issue with 'where exists' clause

marcus_sommer — Thu, 18 Feb 2016 08:06:57 GMT

It's not really clear what you are doing - posting the whole script would be helpful.

Beside them I suggest to consider to build the section access table outside from actually application - it avoids possible side effects and quite probably you want to use these table within other applications, too. Further I could not believe that you have more then a billion of customers which you want to give access to an application - even if there is a customers duplication for the need of a quite detailed authorization I think it's too much as it could be sensible handled and therefore I suggest to rethink your whole approach of authorization.

- Marcus