Post reply

Foreign Keys Negatively Affecting Performance ?

  • July 17, 2008 at 4:25 am

    #70465

    Hi

    Ive got a database, about 800 tables of which about 150 have no FK ri. They do have clustered pk's etc but no FK. Im currently trying to bring these lost sheep into the fold with at least some basic FK to ensure we have no orphaned rows, also so to make understanding the structure easier etc etc

    One our devs was querying if you had a table with highly volatile data that was used as a processing table.. if I suddenly put RI on a table say a couple of FK's .. if he inserts a 1000 rows is there a performance hit for the validation process etc ?

    I must admit its something I've ever considered so I'd be interested to know

    many thanks

    ~si

  • July 17, 2008 at 4:58 am

    #844270

    Obviously there will be some sort of hit, but if things are indexed properly, the hit will be small.

    If you add a child record, SQL server has to make sure the parent exists... as this is a lookup on the primary key of the parent, it will already have an index to support this lookup.

    If you try to delete a parent record, SQL Server has to make sure there are no child records referencing it. If the foreign key column(s) in the child table aren't indexed, SQL Server will have to scan the whole table to check for the existence of a child record... then you will get a performance hit.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply