March 29, 2006 at 2:50 pm
I have two tables in a DB that are generating lots of performance complaints from users. They are not real big, 814,705 and 768,916 rows, but as time has gone on they have taken more and more time to insert and update. There is an enforced relationship between the two that I think is causing these issues. Is there anything I can do to performance tune this without deleteing the enforced relationship between the two? What are the implications if I do delete the enforced relationship?
March 29, 2006 at 3:06 pm
Have you looked at the index fragmentation on the tables. What about statistics. Additional indexes to speed up deletes. I assume that there is a clustered index if not I would put the primary keys as clustered indexes. Just a couple places to look.
Tom
March 29, 2006 at 3:09 pm
Create a nonclustered index on the foreign key column(s). Then the relationship-checking will use the indexes instead of running through the entire table. SQL Server automatically creates an index on the primary key, so that's ready to go.
The implications of dropping the constraint are the possibility of inserting invalid data. If you are about to say "But the developers assured me it's OK because they check themselves before the inserts", then take a look at how they are doing that checking, as that may be part of the bottleneck. If the foreign key is in place, the checking will be automatic, and much faster or - at the very worst - as fast as any explicit pre-insert verification.
-Eddie
Eddie Wuerch
MCM: SQL
March 29, 2006 at 3:36 pm
Exposing my ignorance here. What do you mean by foreign key column?
March 29, 2006 at 5:13 pm
A Foreign Key (FK) column is a column that has a FK constraint defined. Assuming you have these two tables:
Create Table dbo.TableA (ID1 INT NOT NULL PRIMARY KEY, ColA1 VARCHAR(10))
Create Table dbo.TableB (ID2 INT NOT NULL PRIMARY KEY, ColB1 INT NULL CONSTRAINT IE__TableB__ColB1 REFERENCES TableA (ID1))
In this instance, ColB1 is a FK column. As per the previous poster's suggestion, you should index this column for optimal index seek performance, i.e.
CREATE NONCLUSTERED INDEX NC__TableB__ColB1 ON TableB (ColB1)
March 30, 2006 at 10:16 am
> What do you mean by foreign key column?
In an enforced relationship, there are two sides to the equation: The parent table (such as a list of customers), and the child table (such as a list of customer orders) (there are additional more complicated ways of looking at this, but this is what you get most of the time).
The parent table has a Primary Key defined - the column or columns that form the unique value in each row. A Foreign Key is the definition on the child table that enforces the rule that values in that column/those columns must have matching entries in the parent table.
The primary key in the customer/order example above is the CustomerID in the customer table. The foreign key is the CustomerID in orders table.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply