To homepage To homepage
Solved

Load large fact table incrementally takes "long" time.

  • 12 September 2023
  • 1 reply
  • 112 views
G
Rank

Is it normal when loading fact table incrementally with new data, that it can take up to one hour?
Are there any tips to have this incremental load to perform better?

Moving records from staging table to fact table
fact table is 140 million records, new load is around 500 thousand records with new timestamp
 

here is the execution log overview
 

 

the table is not that complex.

17 fields are moved over, from staging to fact table

1 field is from keystore

3 fields are have custom value script

 

table settings are..

 

 


we are running 

TX, version.  6346.1
Azure database, running with 6 cpu
 

icon

Best answer by daniel 12 September 2023, 20:08

View original
Gísli Heiðar

1 reply

daniel
Rank
Userlevel 4
Badge +5

Dear @gislihei ,

What i've found is that the supernatural key might be the bottleneck in this case.

Supernatural keys will check every record every incremental load for changes and that keeps the keys at full integrity, but it will take a long time.

I've posted a similar topic here: 

I'm using the option that @erik.van.mastrigt offers here:
 

using supernatural keys in the dimension and doing a lookup on the business key to the fact table.

Compression might also be an issue, but that really depends on several other factors. You might want to test that but i'm pretty sure the supernatural key is the performance killer here

Hope this helps

= Daniel

Reply


Terms and ConditionsCookie settings