Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

FK on factTables and performance

2 posters

Go down

FK on factTables and performance Empty FK on factTables and performance

Post  nicolad76 Thu Aug 05, 2010 3:59 pm

Hi,
I would like to ask a question about constarints in DW tables. I have read that it is not a good practice to put constraints in DW tables since they overload the server with "useless" data consistency checks. Reading this forum I could notice that the good practice is to define as FK in the fact tables. FK introduce constraints, right? Meaning each insert will have to have all FK validated. When my ETL saves/modifies about 10M rows in one specific fact table, how to FK affect performance?

Thanks
Nicola

nicolad76

Posts : 2
Join date : 2010-08-05

Back to top Go down

FK on factTables and performance Empty Re: FK on factTables and performance

Post  ngalemmo Thu Aug 05, 2010 4:30 pm

Don't confuse logical and physical concepts.

A key, primary or foreign, is a logical concept. It is a designation that identifies the purpose of the attribute(s)... as an identity (primary key) or a reference to an identity (foreign key).

Constraints are a physical concept. A constraint is a rule implemented in a database to enforce content or relationships in a database. A constraint causes the database to perform some action to validate the content or relationship. Constraints are optional and require explicit declaration in the DDL.

If you enforce a FK constraint in the database, when the value of the FK changes or if a new row is inserted, the database is required to validate that key against the PK of the table it references (i.e. it does a lookup). However, since that has already been done through the surrogate key assignment process, that validation is redundant and unnecessarily slows down the load process.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum