June 16, 2009 at 5:23 am
hi,
i have a doubt.
PK-FK constraints will improve the performance with respect to speed
or will it slow down while inserting, updating......?
it's going to be single row insert or update not bulk.
thanks,
regards
viji
June 16, 2009 at 6:45 am
this is what i think ( Maybe SQL experts can shed more light on this) as i am only a newbie.
indexing the column used in your foreign key is a good idea as Without an index, user deletion of a parent row from the database would force the SQL Server query engine to scan the child table referenced in the foreign key to ensure that data integrity is not compromised. The performance of the foreign key CASCADE options (ON DELETE CASCADE, ON UPDATE CASCADE) can be improved dramatically with the use of an index since the engine performs a query to search for the rows that should be automatically deleted or updated. The performance of JOINs between the parent and child tables on the foreign key column is greatly improved as well.
June 17, 2009 at 8:11 am
viji (6/16/2009)
hi,i have a doubt.
PK-FK constraints will improve the performance with respect to speed
or will it slow down while inserting, updating......?
it's going to be single row insert or update not bulk.
There's a very minor overhead for index updates in the case of single record insertion.
That said, the overhead that will hit you like a ton of bricks if you don't have a primary key when you want to read from the table will far exceed the overhead necessary to handle the update to the primary key index.
Unless nobody is ever going to read data from the table (in which case, what's the point of storing it?) not having a primary key in a table is tantamount to not bothering with any indexing at all.
I'll give you an example.
Where I work, I inherited a database with about 3 million records in a table that didn't have a primary key. It had an identity(1,1) column, but nobody had taken the next step of declaring that column the primary key and creating a clustered index on it.
A developer came to me with a horribly performing application. 3 minutes to read about 1000 rows of data out of the table. He had created an index on the table to handle the fields he needed, and couldn't figure out why it took so long. After noticing that I was always seeing table scans and even when I specified the index querying the table was almost 90% of the query's cost, I then looked and noticed the table didn't have a primary key.
So I declared the primary key on the table... about 5 minutes elapsed while that process ran.
After the primary key was fully created on the table the query took under 2 seconds to run.
June 17, 2009 at 1:00 pm
Although SQL Server manifests a PK as an index, and thus has performance implications. Additionally, FKs also have some overhead associated with them.
However, PKs and FKs have nothing to do with performance. Rather they are there to enforce the integrity of the data. (at least conceptually)
June 17, 2009 at 2:40 pm
Primary keys can give a better performance. Because primary key and unique indexes/key garantees uniqueness. Sql server can use this information for generating better plans in equal predicates on the primary/unique key
June 17, 2009 at 8:49 pm
Thanks for your valuable responses
Regards,
Viji
June 18, 2009 at 3:55 pm
[font="Verdana"]Hmmm. All of the answers here are correct. However, they are incomplete. I'll add a little to the picture.
Firstly, you need to differentiate between constraints and indexes. Constraints are something SQL Server uses to enforce some criteria against a table. That includes primary constraints (primary keys), unique constraints, check constraints and so on. Every single time you add a constraint, you are adding additional load to the process of creating data in the table. Okay, in most cases it's not a huge amount of load, but there is a balancing act between how much load you add to inserts, updates and deletes versus how responsive your database is to queries.
SQL Server also uses something called a "clustered index" to handle storage allocation. Without it, the space allocation for a table is known as a "heap", and it's unordered and less efficient. So in this one case, adding an index actually improves the performance of your inserts, updates and deletes.
Most people kill two birds with one stone and have the primary index as the clustered index (and this is the SQL Server default.) That's not a bad design rule, and variation from that rule is infrequent. So adding a clustered primary index actually can improve performance.
Certainly, indexes and unique indexes in particular can dramatically improve query response. A big part of tuning a SQL Server system is around where to add indexes. Generally speaking, those indexes won't be for primary or foreign keys -- you should already have those in place. I haven't checked recently -- can you create a foreign key without a matching index? SQL Server needs to look up the data in the parent table to ensure it exists, so if you didn't have an index there, it could well be rather slow. But from memory, you can only create a foreign key relationship where the parent table has a unique index in place. (Someone please correct me if I've got that wrong.)
After that, every additional constraint you add will slow things down. So you need to choose carefully what level of constraints you build into your physical design. The hardware we run SQL Server on these days is far more capable than it was even 5 years ago, and the impact of adding constraints into a database is minimal. So my rule of thumb with design is now to add in everything I need from the logical design, and then tune if there are performance issues. That's where load testing becomes important.
To summarise: there's no simple answer to your question. If you add primary keys, you may see some performance benefit. If you don't have indexes for your foreign keys (is that even possible?) then you will certainly see performance benefits. But if you go around adding indexes without being careful, you can actually slow down your system.
[/font]
June 19, 2009 at 2:05 am
[font="Tahoma"]Excellent post Bruce.
You are right that the parent column(s) in a foreign-key relationship must have a unique index defined (primary key constraint, unique index or constraint).
The child column(s) do not have to have a supporting index, but one is pretty much essential to good performance - unless the parent table will never have any update or delete activity that affects the relationship.
Paul
P.S. I would just add that the existence of a (trusted and enabled) foreign key constraint makes additional optimization opportunities available to the query optimizer. In some circumstances, an entire join operation can be optimized away because the foreign key guarantees that a parent row exists.[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply