August 15, 2008 at 2:13 pm
I was doing a delete on a record by a field that was always populated. It took about 30 seconds. I could not believe it. then I looked at the execution and it was doing index spools and nested loops to eventually be able to delete the record. I have used these tables before but I have always deleted up and down the tree. What if I want to delete anything referring to DeleteByID. Is there a better design?
CREATE TABLE [dbo].[Table1](
[table1ID] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Note] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentTableID] [uniqueidentifier] NULL,
[DeleteByID] [uniqueidentifier] NULL,
CONSTRAINT [pk_table] PRIMARY KEY CLUSTERED
(
[table1ID] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [fk_table1_table1] FOREIGN KEY([ParentTableID])
REFERENCES [dbo].table1 ([table1ID])
GO
August 18, 2008 at 6:33 am
You'll get the joins between tables because of the referential integrity checks that are a necessary part of any delete. However, putting an index on a column like DeleteId will likely make the delete faster. Otherwise, you're probably getting a clustered index scan in order for it to find that value. With that index, you'll still probably get a key lookup on the sample table and also the joins against the other table, but it should be a lot faster.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2008 at 7:13 am
Second for Grant's advice.
August 20, 2008 at 10:57 am
The use of UNIQUEIDENTIFIER datatype (GUID) occupies more storage than a simple INT. The consequence of such a decision is not only storage but the number of keys that can be crammed into an index extent (and therefore the depth of the index)
- smaller keysize = more keys/extent = fewer levels = faster
as apps conduct I/U operations you can expect bucket splits and unbalanced b-trees (yes even for Clustered Indexes), so you should still conduct reindex/reorg operations (eg. Sunday pm)
look at AdventureWorks.HumanResources.Employee as an example (self-ref via FK_Employee_Employee_ManagerID constraint). In this case it maps back to the PK which is clustered.
as previous posters have noted, DRI is always going to need walking on U/D statements (to avoid orphans or for cascades), so always recommended to have an index on the child-side (eg Sales.SalesOrderHeader.CustomerID)
- guess not an issue for self-ref cases
HTH
Dick
August 26, 2008 at 11:25 am
Besides a valuable information about number of page splits comparing INT and uniqueidentifiers, this article contains some suggestions on "giving blanket advices" 🙂
http://www.eggheadcafe.com/software/aspnet/31678698/what-is-a-page-split-fro.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply