We help IT Professionals succeed at work.

Would a Check Constraint be prefereable to a Referential Constraint in this case?

gswitz
gswitz asked
on
I have a table that I need to perform quickly. It has 6 partitions and needs to load 10M rows a day as quickly as possible. I have been asked to add two referential constraints to the table to assert the two fields have values that fall in a particular set of values. Is it possible that it would be faster to use a check constraint to ensure the new value was one of the set of 30 available values rather than a foreign key, or would the foreign key be faster?

Thanks,

G
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012

Commented:
you can't use a check constraint to do referential integrity.

so it's not a matter of speed.
Most Valuable Expert 2011
Top Expert 2012
Commented:
I guess if you want to hardcode the 30 values into a check constraint you "could" do that, and it will likely be faster for inserts
you'd have to try it both ways to confirm.

However, it's not a very robust solution since you won't "really" have referential integrity, only list-integrity.  If the constraining table changes your list constraint won't be accurate

Furthermore,  the existence of  foreign keys acts as metadata for the optimizer,  so, even if your inserts become faster
using that data in queries once the table is populated might be slower.

Author

Commented:
I only need list integrity. The dimension table that is being created only has one column with a list of the values that I would hardcode into the check constraint. The constraining table exists only for the purpose of constraining the values for the other table. Rather than modifying the dimension table, we would just modify the check constraint. Yes, in this case I would be only trying to optimize the speed of INSERTs not SELECTs.

I'll do the testing and see how it effects the speed.

Thanks, sd.
Most Valuable Expert 2011
Top Expert 2012

Commented:
if list integrity is all you really need, then check constraint probably makes semantic sense as well as being slightly more efficient (but confirm that performance for yourself, don't trust me)
HainKurtSr. System Analyst

Commented:
or create a small table

lookup (id, name, description)

and a table with referential integrity to this lookup table

myTable(id,....,lookupid)

where mytable.lookupid is fk to lookup.id

maintenance will be easy on this one... also you have description and other columns on lookup table so you can use this table in your app to list the options...
Most Valuable Expert 2011
Top Expert 2012

Commented:
check previous posts.

the asker already has a lookup table, the question is whether a check constraint could work faster than using fk to the lookup
I think a more important aspect than choosing between a constraint and a foreign key is to disable or drop the contraint/fk before loading new data and then enabling/recreating it only after the data has been loaded. This will make a a big difference regardless of which method you are using.

Author

Commented:
Dropping the constraint is an interesting point to mention and I appreciate it, but it actually isn't a choice. There are multiple applications inserting heavily in a short window and dropping the constraint would defeat the point of it.

I appreciate everyone's help. I'm going with the FKeys until it is known that performance requirements are not being met.

Thanks,

G