Deleting records from a huge table

Question

Deleting records from a huge table

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

I just ran a test using a large SELECT/INTO while running an UPDATE on the source table. Same effect as previously mentioned. The source table accepts DML... the destination does not.

I'm thinking that the supposed DML problems with SELECT/INTO are pretty much a left over myth... at least for the 2k5 Developer's Edition w/SP3. I'll check the 2k Developer's Edition and the 2k5 Standard Edition later today.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mar1jxm Ten Centuries Points: 1303 More actions · Answer 2

I am not sure I understand why we are comparing UPDATE and SELECT INTO?

Jacob Milter

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Jeff, here was my test:

MMREFW21099: fat table, no indexes, 72667 rows

CompanyID: column on that table with 48 rows with value '20067255'

with clean buffer cache I had 2 window open in SSMS

Window 1:

begin tran

update MMREFW21099

set companyname = 'asdfpiosdfhos woh wo hsdf ohisdfos ioshio sdf hisdfh '

where CompanyID = '20067255'

Window 2:

begin tran

select *

into kgbMMREFW21099

from MMREFW21099

I was unable to get the select into to block the update when I fired the select into first and then immediately fired the update. Note both were doing a table scan. Curiously the update did sometimes block the select into even though I kicked off the update last! I also tried to delete that companyID records while the select into was ongoing. In both cases it blocked the completion of the select into. And if I committed the update/delete, the kgbMMREFW21099 table reflected the update/delete action.

Soooo apparently the select into construct just takes whatever the state of each part of data is when it gets to it without regard for the initial state of the table --> i.e. it does NOT take any form of DML-blocking lock(s). I find this troubling and know I have seen code using select into (and written some myself) that relies on consistent data that is apparently NOT GUARANTEED!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

mar1jxm (2/12/2010)
I am not sure I understand why we are comparing UPDATE and SELECT INTO?

The UPDATE is DML and the general consensus is that DML can't be executed against the source of a SELECT/INTO while the SELECT/INTO is doing it's thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

TheSQLGuru (2/12/2010)
Jeff, here was my test:
MMREFW21099: fat table, no indexes, 72667 rows
CompanyID: column on that table with 48 rows with value '20067255'
with clean buffer cache I had 2 window open in SSMS
Window 1:
begin tran
update MMREFW21099
set companyname = 'asdfpiosdfhos woh wo hsdf ohisdfos ioshio sdf hisdfh '
where CompanyID = '20067255'
Window 2:
begin tran
select *
into kgbMMREFW21099
from MMREFW21099
I was unable to get the select into to block the update when I fired the select into first and then immediately fired the update. Note both were doing a table scan. Curiously the update did sometimes block the select into even though I kicked off the update last! I also tried to delete that companyID records while the select into was ongoing. In both cases it blocked the completion of the select into. And if I committed the update/delete, the kgbMMREFW21099 table reflected the update/delete action.
Soooo apparently the select into construct just takes whatever the state of each part of data is when it gets to it without regard for the initial state of the table --> i.e. it does NOT take any form of DML-blocking lock(s). I find this troubling and know I have seen code using select into (and written some myself) that relies on consistent data that is apparently NOT GUARANTEED!

That's part of the difference in our testing... I didn't use BEGIN TRAN which changes things a bit and may explain why I've had no such blockage.

I agree that it would seem that SELECT/INTO is troublesome for getting consistent data but so would any SELECT unless WITH(TABLOCK) were used.

I'll do some testing with BEGIN TRAN as you've done and see what happens. Thanks for the feedback on all of this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

I agree that it would seem that SELECT/INTO is troublesome for getting consistent data but so would any SELECT unless WITH(TABLOCK) were used.

That doesn't sound right. I thought SELECT (with READ COMMITTED isolation and without NOLOCK) took IS lock(s) that prevented inconsistent data and which would block DML activities.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 7

TheSQLGuru (2/12/2010)
I agree that it would seem that SELECT/INTO is troublesome for getting consistent data but so would any SELECT unless WITH(TABLOCK) were used.
That doesn't sound right. I thought SELECT (with READ COMMITTED isolation and without NOLOCK) took IS lock(s) that prevented inconsistent data and which would block DML activities.

I'll post the code I have so far when I get home tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)