Performance issue with 'where exists' clause

[#1]

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

[MayilVahanan]

HI

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;

[#3]

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.

[#4]

Maybe change

     Load * where Flag = 1;

to

     Load * where Flag <> 1;

?

[MayilVahanan]

Hi

Please Provide a sample data with expected results.

[#6]

Hi,

please find attached.

[marcus_sommer]

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

[MayilVahanan]

Hi

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

];

[#9]

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.

[marcus_sommer]

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