lock manager question

i have a query that deletes a large range of rows but the where clause
is not indexes
delete from instructor where lastteachdate < '2005-01-01'
lastteachdate does not have an index and the instructor table has 100,000
records.
sp_lock shows a table lock. I understand why. Since there is no index on
the
lastteachdate, it must lock everything to search and delete the correct
rows.
there is an index on the table called instructorid. it is a non-clustered
index. it is
the only index on the table (don't yell at me, i inherited this). I was
told not to
add an index on lastteachdate (i can't go into the details why right now).
my question is, if i write code that (a) select instructorid from
instructor where lastteachdate < '2005-01-01'
(b) deletes each instructor by instructorid in a loop, I think I can have
the lock manager
just use row locks and not a table lock. That would allow inserts and
updates of existing rows
without blocking.
Is this a bad idea? I don't know the locking well enough to decipher all
the X locks that I see
in sp_lock. I see locks for the rows and then I see locks on indid = 2
which I think is the
non clustered index.Do you know how many rows you are likely to delete?
If not too many you could insert the instructorid into a table variable (or
'TABLE) and delete using it
declare @.RowsToDelete TABLE (instructorid int)
insert into @.RowsToDelete
select instructorid
from instructor
where lastteachdate < '2005-01-01'
delete from instructor
where instructorid in (select instructorid from @.RowsToDelete)
or
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
or using a derived table may help
delete from instructor
from (
select instructorid
from instructor
where lastteachdate < '2005-01-01'
) as RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
You could also do it in batches by setting ROWCOUNT and using a while loop.
Break out of the loop when @.@.ROWCOUNT after the delete is zero
eg
declare @.RowC
set ROWCOUNT 50
while (1=1) begin
BEGIN TRANSACTION
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
select @.RowC = @.@.ROWCOUNT
COMMIT TRANSACTION
if @.RowC = 0 BREAK
end
set ROWCOUNT 0
Be aware that SET ROWCOUNT will affect any triggers you have on intructors
Paul
"Dodo Lurker" <none@.noemailplease> wrote in message
news:AIGdnacqdfhqm4DYnZ2dnUVZ_tqdnZ2d@.comcast.com...
>i have a query that deletes a large range of rows but the where clause
> is not indexes
> delete from instructor where lastteachdate < '2005-01-01'
> lastteachdate does not have an index and the instructor table has 100,000
> records.
> sp_lock shows a table lock. I understand why. Since there is no index on
> the
> lastteachdate, it must lock everything to search and delete the correct
> rows.
> there is an index on the table called instructorid. it is a non-clustered
> index. it is
> the only index on the table (don't yell at me, i inherited this). I was
> told not to
> add an index on lastteachdate (i can't go into the details why right now).
> my question is, if i write code that (a) select instructorid from
> instructor where lastteachdate < '2005-01-01'
> (b) deletes each instructor by instructorid in a loop, I think I can have
> the lock manager
> just use row locks and not a table lock. That would allow inserts and
> updates of existing rows
> without blocking.
> Is this a bad idea? I don't know the locking well enough to decipher all
> the X locks that I see
> in sp_lock. I see locks for the rows and then I see locks on indid = 2
> which I think is the
> non clustered index.
>|||On Fri, 29 Sep 2006 07:43:40 -0700, "Dodo Lurker" <none@.noemailplease>
wrote:
>i have a query that deletes a large range of rows but the where clause
>is not indexes
>delete from instructor where lastteachdate < '2005-01-01'
>lastteachdate does not have an index and the instructor table has 100,000
>records.
Why do anything? How long does this take to run? I would expect this
only runs a few times a day and deletes less than one percent of
records, shouldn't take more than a couple of seconds at worst.
Using a cursor is almost always a mistake, but you could do a prequery
and get the unique instructorid and delete by that, but I can't see
that running any faster. With the cursor, yes, you might avoid the
table lock, but I'll bet the total execution would be 10x or 100x
worse.
J.