| Author |
Topic |
|
amadobson
Starting Member
7 Posts |
 Posted - 2009-03-23 : 10:19:06
|
| I have fairly complicated data structure which at times can go 4 levels deep all designed using foreign keys as expected. I am having difficulty getting deletion to work from the top level which is an Orders table. Some child tables have cascade deletes on some are dealt with by native C# code. No matter what I try it still takes around 5 minutes to delete an order. The client is using SQL 2000 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-03-23 : 11:55:16
|
| have you got indices on the foreign keys implemented?can you post the execution plan for a simple delete of a parent record? |
 |
|
|
amadobson
Starting Member
7 Posts |
Posted - 2009-03-24 : 09:51:26
|
| Yes I spent hours checking all indices where on foreign keysHow to I get an execution plan to post here ? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-03-24 : 10:55:12
|
| Are you using natural keys, or surrogates?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
amadobson
Starting Member
7 Posts |
Posted - 2009-03-24 : 11:26:15
|
| Sorry I do not understand. What are natural keys and what are surrogates? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-03-24 : 11:29:56
|
| A surrogate would be an Identity, some other integer value, or a GUID most likely.Are your primary keys composite at the lower levels of your table structure?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
amadobson
Starting Member
7 Posts |
Posted - 2009-03-24 : 11:45:25
|
| All primary keys are int identity fields if that makes sense. We have no keys that are made up of more than one field |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-03-24 : 14:19:19
|
| Then my suspicion would fall on the C# code. Why are you using it?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
amadobson
Starting Member
7 Posts |
Posted - 2009-03-25 : 05:44:39
|
| I have been doing all my work on this using Query Analyser so that I can see the execution plans and it still takes 4-5 minutes |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-03-25 : 10:16:58
|
| The execution plan should indicate what steps are taking the most time.Where is the bottleneck?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
amadobson
Starting Member
7 Posts |
Posted - 2009-03-26 : 08:00:20
|
| declare @iOrderID int, @iStoreID intset @iStoreID = (select StoreID from Stores where StoreReference = '005');set @iOrderID = (select OrderID from Orders where OrderNumber='1173757' and StoreID = @iStoreID);delete from ExportedOrderComponentValues where OrderComponentValueID in (select OrderComponentValueID from OrderComponentValues where ComponentID in (select ComponentID from OrderComponents where ProductID in (select ProductID from OrderProducts where OrderID = @iOrderID )))The above line is the bottleneck and both OrderComponentValues and ExportedOrderComponentValues have the correct foreign keys /indexes and bith tables have over 15 million recordsThe execution plan is showing using index seeks to access ExportedOrderComponentValues but yet this takes all of the time |
|
|
|
amadobson
Starting Member
7 Posts |
Posted - 2009-03-26 : 11:51:13
|
| This all turned out to be a red herring as the real issue was that the orders table had a column linked back to its self called RemakeOriginalOrderID. This had no index on it.Thanks for all the help |
|
|
|
|
Performance on cascade deletes