Big table of users (efficient sql select)

[#1]

Hi guys,

I have a User table with almost 500 million records and I have been including it partially in the model like this:

left Keep (Fact)

load * ;

sql select * from Users;

(Both tables join on user ID)

However, this approach looks into the entire 500M record table, before it joins with the users contained in the fact table, which takes quite a while. Is there a better approach? Is there a way to SQL Select with a where clause, using the distinct users from the Fact table, for example?

All help is appreciated.

Thank you.

Nuno

[maxgro · Accepted solution]

you can build a dynamic sql where condition

T: Load

Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as User,

Round(1000*Rand()*Rand()*Rand1) as Expression1;

Load  Rand() as Rand1, IterNo() as TransLineID, RecNo() as TransID

Autogenerate 1000  While Rand()<=0.5 or IterNo()=1;

LOAD chr(39) & Concat( DISTINCT User , chr(39) & ',' & chr(39)) & chr(39) AS UserList

Resident T;

LET vUsers = Peek('UserList');

trace $(vUsers);

and then use in SQL

SQL select *

from Users

WHERE User IN ($(vUsers ));

but for 200k distinct users I think you can't build an efficient where clause;

example in Oracle a condition where user in (user1, user2, ....) will works only for 1000 users

You can try with an OR instead of IN

where (user = 'user1' or user = 'user2' ...............)

Maybe an option to load all the users in Qlik with an incremental load?