Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sasikanth
Valued Contributor III

Composite key performence issue


HI all,

I have 2 fact tables DAILY , SUMMARY (month wise)  andDimensions ,

now i need to create a link table by creating a composite key, i have 7 DIMs so 7 key fields are there,

now i have created a composite key with 7 keys fields ,

Now my question : is there any effect on performence if i create a composite key with 7 key fields?

if then please let me know what are they

Regards

Sasi

13 Replies
Not applicable

Re: Composite key performence issue

Hi Sasi

7-column composite key is not an issue per se (obviously you're using autonumber to convert the key to numeric value, don't you?). You can also try to create two separate docs, one on low level (DAILY) and one aggregated (SUMMARY) and use document chaining to go to detailed doc from summary.

Lukasz

sujeetsingh
Honored Contributor III

Re: Composite key performence issue

The main thing you need for this is that the data type for this key .

Go to this blog

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

sujeetsingh
Honored Contributor III

Re: Composite key performence issue

Avoid using Complex composite keys(when to remove synthetic keys), and instead use the autonumber() function to generate a sequence which uses compact memory.

sasikanth
Valued Contributor III

Re: Composite key performence issue

HI Lukasz

Thanks for the reply,

Can u please provide a link or documents which explain about document chaining .

sasikanth
Valued Contributor III

Re: Composite key performence issue

HI Sijeet

Can i use like this,

autonumber(key1 &'_'& key2 &'_'& key3 &'_'& key4 &'_'& key5 &'_'& key6 &'_'& key7) as CP_KEY,

if so can please exlain the flow...

Not applicable

Re: Composite key performence issue

To setup the document chaining you have to create a trigger with External => Open QlikView Document action and check "Transfer State" checkbox to transfer selections from aggregated document to detail document.

sujeetsingh
Honored Contributor III

Re: Composite key performence issue

That can be but do you really need such big composite key

sasikanth
Valued Contributor III

Re: Composite key performence issue

HI,

Yes, i have tried to reduce fields but i didn`t , so i need to use that one only.

Here one thing that i can`t understand is the FLOW of searching for the record through compsite key ,

when we made selection.

Thanks

Sasi

er_mohit
Honored Contributor II

Re: Composite key performence issue

yes it effects on performance so to make as uniue no. in qlikview to compact memory use

autonumberhash256(Field1,Field2,Field3,Field4,Field5,Field6,Field7) as Key

See it

Autonumber needs one expression as input and autonumberhashXXX can handle more inputs. So you'd use autonumber(Field1&Field2&Field3) which needs to calculate the concatenation of Field1,-2 and -3 first. Autonumberhash can take more inputs: autonumberhash128(Field1, Field2, Field3). More importantly the autonumberhash are likely more efficient in determining if the combined inputs have already been encountered or are encountered for the first time. So they are probably faster in calculating the unique integer.

Community Browser