Slow outer JOIN after delete rows

5 pts.
Tags:
SQL Server 2005
SQL Server errors
Hi, I've tried this on two installations of SQL Server 2005 service pack 2. If I delete rows of a table, an outer join on the empty table is extremely slow...if I truncate it's ok, if I delete with the tablock hint it's ok. Any ideas why? The code to reproduce this follows, the outer join takes <1 second after truncate or using tablock but 64 seconds if using just delete. Thanks.
CREATE TABLE [testtab1](
[col1] [bigint] NULL,
[col2] [bigint] NULL
) ON [PRIMARY]

CREATE TABLE [testtab2](
[col1] [bigint] NULL,
[col2] [bigint] NULL
) ON [PRIMARY]

declare @i int
set @i = 1
while @i <= 20000
begin
insert into testtab1 values(@i,floor(10*rand()))
set @i = @i + 1
end

DECLARE @date datetime
SET @date = getdate()
INSERT INTO testtab2 (col1, col2)
SELECT b.col1,b.col2
FROM testtab1 b
LEFT OUTER JOIN testtab2 a
ON a.col1 = b.col1 AND a.col2 = b.col2
WHERE a.col1 IS NULL OR a.col2 IS NULL
print 'Elapsed time for join/insert after table creation:'+CONVERT(CHAR(8), GETDATE()-@date, 108)

truncate table testtab2

SET @date = getdate()
INSERT INTO testtab2 (col1, col2)
SELECT b.col1,b.col2
FROM testtab1 b
LEFT OUTER JOIN testtab2 a
ON a.col1 = b.col1 AND a.col2 = b.col2
WHERE a.col1 IS NULL OR a.col2 IS NULL
print 'Elapsed time for join/insert after truncate table:'+CONVERT(CHAR(8), GETDATE()-@date, 108)

delete from testtab2 -- with (tablock)

SET @date = getdate()
INSERT INTO testtab2 (col1, col2)
SELECT b.col1,b.col2
FROM testtab1 b
LEFT OUTER JOIN testtab2 a
ON a.col1 = b.col1 AND a.col2 = b.col2
WHERE a.col1 IS NULL OR a.col2 IS NULL
print 'Elapsed time for join/insert after delete:'+CONVERT(CHAR(8), GETDATE()-@date, 108)

Output:

(20000 row(s) affected)
Elapsed time for join/insert after table creation:00:00:00

(20000 row(s) affected)
Elapsed time for join/insert after truncate table:00:00:00

(20000 row(s) affected)

(20000 row(s) affected)
Elapsed time for join/insert after delete:00:01:04

Output if tablock hint is uncommented

(20000 row(s) affected)
Elapsed time for join/insert after table creation:00:00:00

(20000 row(s) affected)
Elapsed time for join/insert after truncate table:00:00:00

(20000 row(s) affected)

(20000 row(s) affected)
Elapsed time for join/insert after delete:00:00:00

Answer Wiki

Thanks. We'll let you know when a new response is added.

By running sp_who3 on the spid running the above code I see that my processed is being paused with a wait type of SOS_SCHEDULER_YIELD.

This basically means that the scheduler that you are trying to use is busy doing something else, possibly flushing the delete to disk?

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following