x

Get count while insert is still running

I'm running something like this: ___________________ more:

waitfor delay '00:00:05'

insert top (1000) into TableB Select * from TableA A where A.ID not in (select ID from TableB) -- not already inserted

if @@rowcount > 0 goto more ____________________ Assuming there are 100,000 records to be inserted (100 loops), should I expect to get a result when it's halfway through if I run:

select count(*) from TableB with (nolock)

Or will my count be 0 until it completes ? It's been running 2 hours and count is still 0 ! (No blocking processes)

Update: I cancelled the insert, modified some code, and now it seems to be working .... basically: inefficient code.
more ▼

asked Jul 26, 2011 at 09:34 AM in Default

homebrew01 gravatar image

homebrew01
252 3 4 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
@homebrew01 With the code as written, yes, you should expect to get a result with or without a NOLOCK hint.
more ▼

answered Jul 26, 2011 at 10:59 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

As @Oleg stated in comment you should receive count whenever you use the NOLOCK hint or not.

In case you use the NOLOCK hint you can receive partial counts even during the insertion of the records.

Without the NOLOCK hit, you will receive counts only after each batch of 1000 records as the records being inserted will be in not committed transaction and eventually locks will be placed on the table/index which will block the count until the current insert batch finish.
more ▼

answered Jul 26, 2011 at 12:38 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Pavel is completely right. Just let me add the obligatory note that using NoLock runs the risk of a dirty read. Of course, if all you are trying to do is get a status indicator, then that doesn't really matter. I personally use a technique like that as a status tracker all the time on large inserts.
Jul 26, 2011 at 04:16 PM TimothyAWiseman
Exactly @TimothyAWiseman. For seeing status and progress without any blocking the NOLOCK hit is excellent. I'm also using this approach for large inserts to see, that everything is running correctly and the inserts are not being blocked by some kind of lock. Also using this approach its possible to estimate the remaining time to finish large insert.
Jul 26, 2011 at 11:07 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x106
x12

asked: Jul 26, 2011 at 09:34 AM

Seen: 1965 times

Last Updated: Jul 26, 2011 at 11:43 AM