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.
| Author |
Topic |
|
lomvardi
Starting Member
7 Posts |
 Posted - 2002-12-19 : 14:29:48
|
| I have a set of child tables from which I need to delete records based on a subquery to a parent table. I am selecting the same records from the parent table for each child table delete. I'm wondering if there is a more efficient way to do all these deletes without requerying the parent table in each delete statement. My parent table has over 24 million (!!!) records, so you can see why I'm interested in this. Here's my current code (all in a stored proc). DELETE FROM CHILD1 WHERE EXISTS (SELECT * FROM PARENT WHERE PARENT.RecordID = CHILD1.RecordID AND PARENT.EventDate >= @BeginDate AND PARENT.EventDate <= @EndDate); DELETE FROM CHILD2 WHERE EXISTS (SELECT * FROM PARENT WHERE PARENT.RecordID = CHILD2.RecordID AND PARENT.EventDate >= @BeginDate AND PARENT.EventDate <= @EndDate);Any ideas?Thanks,Chris |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-12-19 : 14:56:49
|
| In SQL2K, you can have cascade deletes. Which version are you running? |
 |
|
|
lomvardi
Starting Member
7 Posts |
Posted - 2002-12-19 : 15:06:53
|
| I'm running SQL2k with SP2. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2002-12-20 : 14:03:47
|
You might also try converting the subquery to an inner join, thats faster in many circumstances.DELETE CHILD1 FROM CHILD1 INNER JOIN PARENT ON PARENT.RecordID = CHILD1.RecordID WHERE PARENT.EventDate >= @BeginDate AND PARENT.EventDate <= @EndDateTry timing both, and see which one is faster for your situation. Oh, and I hope both columns are indexed!  OS |
|
|
|
lomvardi
Starting Member
7 Posts |
Posted - 2002-12-20 : 14:58:21
|
| Yes, I've considered the INNER JOIN as well as the WHERE EXISTS option, but the real issue is repeating the parent table scan which I want to avoid. I'm wondering if creating a temporary table of all the Parent table RecordID values for the given date range and then joining that temp table to my child tables would be reasonable? And faster?Chris |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2002-12-20 : 15:42:03
|
| Two points:Since you are deleting just the dependent rows and not the parent rows, a cascading delete will not help you. It really depends on the number of rows in the dependent table. That is, if you only have 1000 rows in CHILD1, then the parent table will only be accessed 1000 times (hopefully via the index that supports the primary key RecordID). If you are concerned about performance, you might try creating an index on PARENT on columns RecordID and EventDate. |
|
|
|
lomvardi
Starting Member
7 Posts |
Posted - 2002-12-20 : 15:51:12
|
| Parent table has over 25 million rows. Child tables range from 100's of thousands to 300 million. Tables are already indexed appropriately. Once child rows are deleted, then parent rows are to be deleted. So my question about using a temp table remains or even the new SQL2K Table data type. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
|
|
|
|
|
Multiple Deletes using same subquery?