By: Ben Snaidero | Comments (1) | Related: > Database Design
Problem
In my never ending quest of improving SQL Server database performance I often think about how foreign key constraints can slow down insert performance. While everyone understands why foreign key constraints are required, to protect referential integrity, what if there was some other method that we could use to keep our referential integrity intact and also improve performance?
In this tip we will look at the pros and cons of using a check constraint in place of the usual foreign key constraint that would be used to protect the referential integrity between two tables.
Solution
Before we dig into the actual details of this tip, just in case anyone is really new to databases you can read more about constraints: check, foreign key, and others by following this link.
For this tip we will use a simple foreign key to a lookup table as an example and convert this foreign key to a check constraint on the main table.
Setup SQL Server Foreign Key Example
Here is the T-SQL for a typical foreign key relationship.
create table lookup_fk (id int not null, description varchar(10));
create table main_fk (id int not null identity (1, 1), lookupid int, col1 int,col2 int,col3 varchar(100));
alter table lookup_fk add constraint pk_lookup primary key clustered (id);
alter table main_fk add constraint pk_main primary key clustered (id);
alter table main_fk add constraint fk_main_lookup foreign key
(lookupid) REFERENCES lookup_fk (id) on update cascade on delete cascade;
create index ix_main_fk_lookupid on main_fk (lookupid);
Setup SQL Server Check Constraint Example
We can replicate this table structure using a check constraint instead of the foreign key constraint by using the following T-SQL. Note that with the check constraint we do have to specify the list of values that make up the check constraint. This would have to be updated on the fly (as you’ll see later) but for this test we will just populate the check constraint ahead of time.
create table lookup_cc (id int not null, description varchar(10));
create table main_cc (id int not null identity (1, 1), lookupid int, col1 int,col2 int,col3 varchar(100));
alter table lookup_cc add constraint pk_lookup_cc primary key clustered (id);
alter table main_cc add constraint pk_main_cc primary key clustered (id);
alter table main_cc add constraint ck_main_cc_lookupid check
((lookupid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)));
create index ix_main_cc_lookupid on main_cc (lookupid);
Load Test Data to SQL Server Tables
Now that we have our table structure created, we can load some test data. Here is the T-SQL to load both the main and lookup tables.
declare @cnt integer
declare @incnt integer
select @cnt=1
while @cnt < 20
begin
insert into lookup_fk values (@cnt,'count ' + cast(@cnt as varchar));
insert into lookup_cc values (@cnt,'count ' + cast(@cnt as varchar));
select @incnt=1
while @incnt < 100
begin
insert into main_fk (lookupid,col1,col2,col3) values
(@cnt,@cnt,@cnt,'dummydatadummydatadummydatadummydata' + cast(@cnt as varchar));
insert into main_cc (lookupid,col1,col2,col3) values
(@cnt,@cnt,@cnt,'dummydatadummydatadummydatadummydata' + cast(@cnt as varchar));
select @incnt=@incnt+1
end
select @cnt=@cnt+1
end
SQL Server Referential Integrity Test
The first thing we need to ensure is that our referential integrity still works as expected. In the above script our lookup table is loaded id values up to 19 so let’s try to insert a value into the main table with a lookupid value greater than 19.
insert into main_fk (lookupid,col1,col2,col3) values
(20,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));
insert into main_cc (lookupid,col1,col2,col3) values
(20,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));
After running the above T-SQL, we can see we get a similar error message to what we see with the foreign key example and that no records were inserted into our table.
Msg 547, Level 16, State 0, Line 40 The INSERT statement conflicted with the FOREIGN KEY constraint "fk_main_lookup". The conflict occurred in database "test", table "dbo.lookup_fk", column 'id'. Msg 547, Level 16, State 0, Line 41 The INSERT statement conflicted with the CHECK constraint "ck_main_cc_lookupid". The conflict occurred in database "test", table "dbo.main_cc", column 'lookupid'.
SQL Server Insert Performance Test
Now that we know the referential integrity still works as expected we can look into the performance. First let’s take a look at the insert performance by running a single insert statement against each table. Here is the T-SQL.
insert into main_fk (lookupid,col1,col2,col3) values
(15,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));
insert into main_cc (lookupid,col1,col2,col3) values
(15,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));
Using SQL Profiler we can see below that inserting into the table with the check constraint performs fewer reads than the table with the foreign key as it no longer has to query the lookup table.
You can also confirm this by using ‘set statistics io on’ before executing the query. You can see below from how this output shows that the second query against only reads from the main table.
Table 'lookup_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Table 'main_fk'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Table 'main_cc'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
SQL Server Update Performance Test
Let’s perform a similar test using an update statement. We can update the lookupid value for a single record in each of the main tables. Here is the T-SQL for these two statements.
update main_fk set lookupid=10 where id=231; update main_cc set lookupid=10 where id=231;
Just as we saw with the insert statement the update statement is slightly faster and performs fewer reads.
Using the output from having ‘set statistics io on’ enabled we can again confirm that it’s the extra reads of the lookup table that cause the additional resources to be used by the foreign key structure query.
Table 'lookup_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Table 'main_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Table 'main_cc'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
SQL Server Delete Performance Test
Performing a delete using our new check constraint structure is definitely more involved than our standard foreign key. Since the foreign key on the main_fk table was created using the cascade clause, if we want to remove an entry from the lookup table (and subsequently the main_fk table) then all we have to do is perform the delete on the lookup table as follows.
begin transaction; delete from lookup_fk where id=19; commit transaction;
On the check constraint structure there is much more we have to take care of as records will have to be removed from both tables manually and the check constraint on the main_cc table needs to be recreated with the corresponding lookupid value removed. The DML and DDL for all of these commands are below.
begin transaction;
delete from lookup_cc where id=19;
delete from main_cc where lookupid=19;
alter table main_cc drop constraint ck_main_cc_lookupid ;
alter table main_cc with nocheck add constraint ck_main_cc_lookupid
check ((lookupid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)));
commit transaction;
Using SQL Profiler we can see below that in this case although the check constraint has to performs ~10% more reads than the foreign key structure it runs twice as fast and uses half as much cpu.
Note: RowCount does not include deletes made by cascade.
One thing I would also like to note here is that adding a value to the lookup table would probably see similar a performance difference as we have seen in this test as in that case we would also have to drop and recreate the check constraint on the main_cc table.
Summary
This shows that for at least this simple use case we can get improved performance by using a check constraint. It would be interesting to test this further on a larger scale to see if the same pattern exists. That said, I think using this method would ultimately come down to how often you are adding/removing lookupid values vs how many data values are being added to the main table as it does add some complexity if you are adding/removing lookupid values very frequently.
Next Steps
- Read more information on SQL Server Performance
- Read more information on constraints in SQL Server
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
