Foreign Key makes my delete sloooow...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Foreign Key makes my delete sloooow...

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2005-09-26 : 07:07:18

Help!

I have a stored proc that is running really slow.

I have isolated the problem to a single delete statement


DELETE FROM
	dbo.DISTRIBUTIONLINES 
FROM
	dbo.DISTRIBUTIONLINES DL
INNER JOIN 
	@tempDISTRIBUTION TD
ON
	TD.uidproduct = DL.uidproduct
AND
	DL.uiddistribution = @distribution

which is fairly simple.

Yet it is running DOG slow, and I have looked at the execution plan and found that this is becuase of a foriegn key constraint; all the foreign keys have indexes and I have even declared uidproduct in the @temp table as a primary key

Execution Plans

WITH FOREIGN KEY - execte time = 2:11


       |--Assert(WHERE:(If NOT(([Expr1008] IS NULL)) then 0 else If NOT(([Expr1009] IS NULL)) then 1 else NULL))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([DISTRIBUTIONLines].[uiddistributionline]), DEFINE:([Expr1009] = [PROBE VALUE]))
                 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([DISTRIBUTIONLines].[uiddistributionline]), DEFINE:([Expr1008] = [PROBE VALUE]))
                 |    |--Sort(ORDER BY:([DISTRIBUTIONLines].[uiddistributionline] ASC))
                 |    |    |--Clustered Index Delete(OBJECT:([MY_DATABASE].[dbo].[DISTRIBUTIONLines].[PK_DISTRIBUTIONLines]))
                 |    |         |--Top(ROWCOUNT est 0)
                 |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([DL].[uidproduct]))
                 |    |                   |--Clustered Index Scan(OBJECT:([MY_DATABASE].[dbo].[DISTRIBUTIONLines].[PK_DISTRIBUTIONLines] AS [DL]), WHERE:([DL].[uiddistribution]=[@distribution]) ORDERED)
                 |    |                   |--Clustered Index Seek(OBJECT:(@tempDISTRIBUTION AS [TD]), SEEK:([TD].[uidproduct]=[DL].[uidproduct]) ORDERED FORWARD)
                 |    |--Row Count Spool
                 |         |--Index Scan(OBJECT:([MY_DATABASE].[dbo].[DISTRIBUTIONDALines].[DDALines_uidda]),  WHERE:([DISTRIBUTIONDALines].[uiddistributionline]=[DISTRIBUTIONLines].[uiddistributionline]))
                 |--Row Count Spool
                      |--Index Seek(OBJECT:([MY_DATABASE].[dbo].[DISTRIBUTIONGRADELINES].[IX_DISTRIBUTIONGRADELINES]), SEEK:([DISTRIBUTIONGRADELINES].[uiddistributionline]=[DISTRIBUTIONLines].[uiddistributionline]) ORDERED FORWARD)

WITH FOREIGN KEY dropped -- execute time = 1 sec


       |--Assert(WHERE:(If NOT(([Expr1006] IS NULL)) then 0 else NULL))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([DISTRIBUTIONLines].[uiddistributionline]), DEFINE:([Expr1006] = [PROBE VALUE]))
                 |--Sort(ORDER BY:([DISTRIBUTIONLines].[uiddistributionline] ASC))
                 |    |--Clustered Index Delete(OBJECT:([MY_DATABASE].[dbo].[DISTRIBUTIONLines].[PK_DISTRIBUTIONLines]))
                 |         |--Top(ROWCOUNT est 0)
                 |              |--Nested Loops(Inner Join, OUTER REFERENCES:([DL].[uidproduct]))
                 |                   |--Clustered Index Scan(OBJECT:([MY_DATABASE].[dbo].[DISTRIBUTIONLines].[PK_DISTRIBUTIONLines] AS [DL]), WHERE:([DL].[uiddistribution]=[@distribution]) ORDERED)
                 |                   |--Clustered Index Seek(OBJECT:(@tempDISTRIBUTION AS [TD]), SEEK:([TD].[uidproduct]=[DL].[uidproduct]) ORDERED FORWARD)
                 |--Row Count Spool
                      |--Index Seek(OBJECT:([MY_DATABASE].[dbo].[DISTRIBUTIONGRADELINES].[IX_DISTRIBUTIONGRADELINES]), SEEK:([DISTRIBUTIONGRADELINES].[uiddistributionline]=[DISTRIBUTIONLines].[uiddistributionline]) ORDERED FORWARD)

I really dont want to have to drop the constraint, but the performance is unacceptable and I may have to unless I get struck by divine inspriation or sqlteam can help!

Any ideas gratefully received

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-26 : 07:25:43

Put an INDEX on the column in the FK on the child table.
....CASCADE delete is important to prevent orphan child records.

Alternatively IF you are sure, there are NO child records, suspend the constraint, activate the delete and reenable the constraint.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources