I heard that SQL is faster if table relationships are defined.
Is this true?
Or maybe it's slower, I would like to know.
+3
A:
SQL is not faster if table relations are defined.
Indexes improve performance; referencial integrity (table relations) ensures the quality of data according to business requirements.
OMG Ponies
2009-08-29 20:51:26
+1 and often data integrity (which RI enforces) is a lot more important than sheer speed ! What good is it if I give you a wrong answer really really fast??
marc_s
2009-08-29 21:37:21
+4
A:
My guess is that you're talking about foreign keys. This is also known as referential integrity, and is one kind of constraint. Foreign keys are not the only kinds of constraints--you can have unique and check constraints, as well. Anyway, referential integrity is slightly slower for inserts, and not faster at all for selects.
The reason is that it has to check inserted values to ensure that they exist in the other table.
If you want to improve the performance of select queries, you want to put indices on columns that you'll be joining and filter on. However, indices do come at a cost, as they slow down inserts, updates, and deletes, because the indices have to update every time the table changes like that.
So, if your table is high volume insert/update, don't add too many indices. If your table is predominantly select, use indices where you can. The Database Engine Tuning Advisor can help you define those indices for some of your most common queries, as well.
Ensure that you use the Query Execution Plan when running your queries (Ctrl + L in SSMS) so that you can see what SQL Server is doing. You want as many seeks as possible, as that means that it's able to use an index most efficiently!
Eric
2009-08-29 20:54:54