Re: constraints using and performance question

While adding it is important to have data consistency in your tables, it is generally true that adding contraints and foreign keys to tables will create performance bottleneck when it comes to data loading. I'm not sure how big an impact it is but there is a difference.

If you can ensure consistent data loading at the ETL level, it is advised to remove the constraints in a datawarehouse type of schema. However this is not the same with OLTP schemas as you will need the database contraint to prevent wrong insertions. In this case it is best practise to add the contraint since it will not make much difference to performance as OLTP then to add a single row at a time.



On Thu, 30 Aug 2007 06:39:45 -0700, andrijz <Andriy.Zabavskyy@xxxxxxxxx> wrote:

I've heard an opinion that adding foreign keys or check constraints at
runtime can be a huge performance bottleneck. (I mean for example
foreign keys in fact table and runtime process of data loading to data
warehouse database)

I don't think so, in my opinion using at least foreign keys is always
good practice and data consistency is ever more important than time of
data loading which is relatively rare.

Has anybody an experience of huge performance bottleneck during
process of data populating?

Thanks an advance,
Andriy




--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
.

Loading