Foreign Keys Slowing down Delete

Question

I have a table X which has an auto-incremented ID column as its primary key. I have other tables A, B, C and D that compliment the info in table X. Each of these have to contain a column that references the ID from table X. I have done that and in my code (Java) and I have a way of returning the ID of each entry to table X and using that when inserting to the other tables. All is working well.

Now, I've been advised to assign those ID columns on tables A, B, C and D as FOREIGN KEYS because "it's the right thing to do". I did that. Now deleting rows from table X takes an incredible amount of time to complete. Insertion to the other tables takes longer too.

Please don't get me wrong, I know why Foreign Keys are relevant to specify relationships of tables on a DB. But it's starting to seem only ceremonial rather than actually relevant especially as my transactions are becoming slower.

Questions:

1. Is it worth it to lose some performance in a bid to keep relationships officially specified even though it's not that necessary?

2. Is there any way I can speed up my transactions and still keep the FOREIGN KEY specifications.

Thanks.

REPLIES

Here is how the tables were created.

CREATE Tables SQL:

CREATE TABLE [dbo].[MainTableX](
    [col1] [smalldatetime] ,
    [col2] [varchar] (20) ,
    [ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_MainTableX] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)
)
GO


CREATE TABLE [dbo].[RelatedTableA](
    [differentID] [varchar] (50),
    [detail1] [varchar] (40),
    [detail2] [varchar] (40),
 CONSTRAINT [PK_RelatedTableA] PRIMARY KEY CLUSTERED 
(
    [differentID] ASC
)
GO

-- Tables B, C D are pretty much similar to table A

Add Foreign Keys SQL:

ALTER TABLE RelatedTableA ADD ID INT 
CONSTRAINT fk_refTableX_A FOREIGN KEY (ID)
REFERENCES MainTableX(ID) 
GO
-- Same thing with the other related tables

SOLUTION

I made the Foreign Key columns an index. Now my queries are fast again.

Create nonclustered index IX_RelatedTableA 
on RelatedTableA (ID) 
GO

Answer 1

If the FOREIGN KEY columns are correctly indexed (which should happen automatically with the declaration, I believe) then you should see at worst a small performance hit. Please list the CREATE TABLE SQL for each of the tables because it sounds like something is wrong there.

SQL Server does not automatically create an index on the FK column(s), so make sure you do this yourself.

The purpose of using FOREIGN KEY relationships is not to "officially declare" anything (or not just to do that, in any case). Instead, it gives the database engine sufficient information to enforce the integrity of your data. This means that you can't erroneously have your application add or delete records such that a relationship is violated. In addition, no other application using the database (such as Management Studio) can do so, either. It's for this reason -- guaranteed enforcement of rules by the database engine -- that declaring constraints is important.

Answer 2

The foreign keys are not your problem. And you do not want to remove them. When you delete a row from table X, I assume you are first deleting rows from tables A, B, C and D first? You would have to if you have FK's established. How are you removing the rows from these tables? From within your Java application? If so, it would be significantly faster to set up your FK's to do a cascading delete. That way, you can make a single call to delete the row in table X and all the child rows get deleted automatically by SQL Server. You would save yourself four trips to the DB for each deletion from table X.

BTW, there is more value ot FK's than just maintaining data integrity (which is huge). If you intend to ever start using an ORM (e.g., Entity Framework), having FK's in place will make your life much easier.