Performance tuning: high unique number & remove a link table?

[Huiying]

Hi,

I'm trying to tune a slow app (171m rows), complicated data model and complex script. 

Question 1: There are 2 fields with long ID numbers, they have high unique values. They are used in charts for count. Is autonumber() a good and safe way to go?

Question 2: A link table linking fact table and dimension table, using an id field which has high unique values. This link table is 2nd largest table because of the id field. Not sure how to remove the link table. Do you have any suggestion how to tune it?

BEst regards,

Susan

[dplr-rn]

Did you try Robs document analyzer?

regarding question 1 auto number should help but not sure how much without knowing more but its definitely worth a try.

question 2 is not easy to help without knowing more about the data model. can you share an example of the columns in the fact, link and dimension tables.

[Huiying]

Yes, i used DA as guidance for performance tuning.

We have 23 fields with over 1m unique value. I'm looking into each of them and trying to find a solution to reduce unique values. For the mentioned 2 fields, they are both in fact table, and they are only used in count... not sure what kind information I should give, can you help by throwing some questions?

Our fact table is somehow complex and generated by centrally managed scripts, not sure whether we can modify it. The fact table is missing field 1 which is key field of table 1. Then the easiest way to link fact table and table 1 is to create a link table. 

Say, there is rowId in Fact table. there is contactId in Contact table. Link table has 2 columns: rowId and contactId. But the problem is, rowId has 95.6m unique value. this link table is an expensive solution. Not very sure what is the result if we concatenate the Contact table to Fact table, but still rowId will be needed anyway...

[Huiying]

business said we have to keep the rowId. so now I have to balance which is better:

1. concatenate contactId to our huge monster fact table

or 

2. keep the Link table as it is. I guess this means 2 table + 2 symbol table with 95.6m unique values in each of them?

Then maybe first solution is better?

How do you think?

[dplr-rn]

i would put the contactid in the main fact table. Will definitely help wrt speed because of the less number of hops

[Brett_Bleess]

Hey Susan, here is a Design Blog post that could be somewhat helpful as well:

https://community.qlik.com/t5/Qlik-Design-Blog/Concatenate-vs-Link-Table/ba-p/1467569

Might help explain the cost of the Link Table better etc. and maybe give you some other ideas, there are hundreds of posts on the Design Blog area too, so do not forget to poke around there too, there are likely other posts that might help too.

Regards,
Brett