March 13, 2012 at 1:35 pm
I already tried separation of select, it is the Index scan in the select that is taking most of the time. I think as engine has to check all records of table B for each record of table A which is list foreach() loop in another foreach(). That is taking most of the time.
I already tried TOP. Top is not helping because the subset that I'm trying to delete is really small. So engine has to do that foreach() anyways for almost all the records until it finds even 1 record.
March 14, 2012 at 6:21 am
And what about this? I've often seen left outer join with a "is null" check on (one of) the key columns of the right table in the where clause outperform any exists check when the number of non-matches is very small compared to the number of matches.
delete top (100) ta
from A ta
left outer join B tb on (tb.PersonID = ta.PersonID)
where tb.PersonID is null
The top (100) is only there to avoid that a single run attempts to delete too many rows at once, thereby potentially blocking your concurrent processes for a long time. i.e. you can replace the 100 by any number you like or leave the top clause off entirely at the risk of blocking all processes for some time when the day comes you need to delete say .5 million rows.
March 14, 2012 at 7:36 am
Ok then. Why don't you approach the problem from the other end: if finding the 'holes' in your large set of table A records is the issue, why not keep track of those few rows that need to be removed? i.e. create 2 triggers, one on table A and one on table B. Plus create a table C which has only a column PersonID. Both triggers modify table C in such a way that always only the PersonID's that are in table A but not in table B are stored in table C.
Then when you need to execute the clean-up, all you need to do is delete from A where PersonID in (select PersonID from C). The trigger on A will update C accordingly, also for clean up process. This way you've eliminated the search for the surplus rows in A from the delete operation.
March 14, 2012 at 7:48 am
@r.P.Rozema
Thanks for the reply.
The foreign key of A goes into 2 more tables just like table B. But those are not huge tables like table B. So idea of creating table C would work but the question is that 1) I do not like SQL server triggers. (Currently I have zero triggers in my database) 2) Do I want to create and maintain another table in the database just for the clean-up stuff.
@All
But all these are really good ideas. That's what I want.
March 14, 2012 at 8:43 am
mayur birari (3/14/2012)
@R.P.RozemaThanks for the reply.
The foreign key of A goes into 2 more tables just like table B. But those are not huge tables like table B. So idea of creating table C would work but the question is that 1) I do not like SQL server triggers. (Currently I have zero triggers in my database) 2) Do I want to create and maintain another table in the database just for the clean-up stuff.
@All
But all these are really good ideas. That's what I want.
1. What is wrong with triggers?
2. One more table in database to help you with database maintenance should not scare you up!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply