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

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@gmail.com>
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/