FK on factTables and performance

View previous topic View next topic Go down

FK on factTables and performance

Post  nicolad76 on 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

View user profile

Back to top Go down

Re: FK on factTables and performance

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top


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