October 14, 2010 at 9:49 am
I have a long running insert statement that sometimes needs to be run during business hours, but can block users.
Originally it was a basic: insert into TableA (Col1, Col2 ...) select Col1, Col2 ... from TableB where condition = 1
It usually inserts 100,000 - 300,000 records when it runs once or twice a month and takes up to 30 minutes.
I modified it a few months ago, attempting to batch the insert, and give a 5 second break:
InsertMore:
WAITFOR DELAY '00:00:05'
INSERT top (5000) into TableA (Col1, Col2 ...) select Col1, Col2 ... from TableB
where condition = 1
and id NOT IN (select id from TableB ) -- Don't insert the same records again
if @@rowcount > 0 goto InsertMore
It's run 3 or 4 times since modification and there hasn't been an issue until today. Seems it was blocking users until it had finished all the inserts. Do I need a "GO" between each batch of inserts to break it up more definitively ? Better method ?
October 14, 2010 at 11:06 am
You might be having a problem with the "where not in" part that queries the target table.
Have you tried logging the inserted rows in a temp table, using the Output clause? That will probably reduce locking.
You might also look into the various isolation levels. I'm not sure those will help, but they might.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2010 at 12:35 pm
Sorry, I haven't used OUTPUT so I don't quite follow how that would help. (reading up on it now)
I should clarify, that TableA that data is being selected from is a staging table.
I'm experiencing blocking on TableB that is having data inserted into it. Sorry for any confusion.
October 15, 2010 at 4:58 am
If it is blocking users until all inserts have finished you probably have a transaction running that spans all inserts. How is the script started?
October 15, 2010 at 11:35 am
homebrew01 (10/14/2010)
Sorry, I haven't used OUTPUT so I don't quite follow how that would help. (reading up on it now)I should clarify, that TableA that data is being selected from is a staging table.
I'm experiencing blocking on TableB that is having data inserted into it. Sorry for any confusion.
Yeah, I understood where the blocking was.
What I mean by using an Output clause, is something like this:
create table #T (
ID int primary key);
insert into dbo.TableA (list of columns)
output inserted.ID into #T (ID)
select (list of columns)
from dbo.TableB
where ID not in (select ID from #T);
Because the Select doesn't reference TableA, it will often reduce locking in TableB, because it can do an outside lookup without sharing rows.
Of course, if you can simply select a range of ID numbers that need to be inserted, and use those mathematically, that's usually much better.
declare @NextID int;
select 1
while @@rowcount > 0
begin
select @NextID = max(ID)
from dbo.TableA;
insert into dbo.TableA (list of columns)
select top 5000 (list of columns)
from dbo.TableB
where ID > @NextID
order by ID;
end;
Will either of those do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply