Bulk Insert Data? Data in Millions

[adi]

Hi all,
Hope there is a quick fix for this:

I am inserting data from one table to another on the same DB. The
insert is pretty simple as in:
insert into

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable...

This inserts about 4 millions rows in one go. And since I had the
'cannot obtain lock resources' problem, several methods were suggested
by some web sites:
1) one to split the insert into smaller chunks (I have no idea how I
can spit a insert to insert only n records at a time..)

2)to use waitfor - which I did but did not fix the error.

3)use bulk insert (in t-sql) - I dont know how to do this?

As I see I am simply trying to move data from one table to another
(ofcourse lots of data) in SQL Server 2000 and I dont see one simple
solution to the locking problem.

any ideas on how best I can do this will save my day!

thanks all.

 

[David Browne]

[crossposting to microsoft.public.sqlserver.programming]

Hi all,
Hope there is a quick fix for this:

I am inserting data from one table to another on the same DB. The
insert is pretty simple as in:
insert into

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable...

This inserts about 4 millions rows in one go. And since I had the
'cannot obtain lock resources' problem, several methods were suggested
by some web sites:
1) one to split the insert into smaller chunks (I have no idea how I
can spit a insert to insert only n records at a time..)

Bad Idea.

2)to use waitfor - which I did but did not fix the error.

Bad Idea.

3)use bulk insert (in t-sql) - I dont know how to do this?

Won't help.

As I see I am simply trying to move data from one table to another
(ofcourse lots of data) in SQL Server 2000 and I dont see one simple
solution to the locking problem.

You are doing the right thing. What error exactly are you getting? Do you
have a lock timeout set?

You might add a query hint to obtain an exclusive table lock on the
destination table for the duration of the load.

insert into datatable with(tablockx) (field1, field2, field3)
select a1,a2,a3 form temptable

David

 

[adi]

Good, the error I got is:

Server: Msg 1204, Level 19, State 1, Procedure insert_into_datatable,
Line 11
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system
administrator to check the SQL Server lock and memory configuration.


I tried something like this:
insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable WITH (NOLOCK)

what is the table lock in the insert statement table?
Can you please eloborate on the "with(tablockx)"

thanks a lot!

 

[Guest]

Try creating a DTS package
--
Thomas

[crossposting to microsoft.public.sqlserver.programming]

Hi all,
Hope there is a quick fix for this:

I am inserting data from one table to another on the same DB. The
insert is pretty simple as in:
insert into

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable...

This inserts about 4 millions rows in one go. And since I had the
'cannot obtain lock resources' problem, several methods were suggested
by some web sites:
1) one to split the insert into smaller chunks (I have no idea how I
can spit a insert to insert only n records at a time..)

Bad Idea.

2)to use waitfor - which I did but did not fix the error.

Bad Idea.

3)use bulk insert (in t-sql) - I dont know how to do this?

Won't help.

As I see I am simply trying to move data from one table to another
(ofcourse lots of data) in SQL Server 2000 and I dont see one simple
solution to the locking problem.

You are doing the right thing. What error exactly are you getting? Do you
have a lock timeout set?

You might add a query hint to obtain an exclusive table lock on the
destination table for the duration of the load.

insert into datatable with(tablockx) (field1, field2, field3)
select a1,a2,a3 form temptable

David

 

[adi]

Well, I did create a DTS package with the same insert statement.
It too gave me the same error.

I am in a lock myself

thanks

 

[David Browne]

Try creating a DTS package

Bad idea. A single INSERT is better.

David

 

[adi]

David,
Did you see the error message I am getting?

Server: Msg 1204, Level 19, State 1, Procedure insert_into_datatable,
Line 11
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system
administrator to check the SQL Server lock and memory configuration.


BTW, I am trying out your tablock hint on about 1/2 million rows. We
will see how it goes.

thanks

 

[David Browne]

Good, the error I got is:

Server: Msg 1204, Level 19, State 1, Procedure insert_into_datatable,
Line 11
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system
administrator to check the SQL Server lock and memory configuration.


I tried something like this:
insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable WITH (NOLOCK)

what is the table lock in the insert statement table?
Can you please eloborate on the "with(tablockx)"

thanks a lot!

Ok this problem probably means that you query starts out with a lot of
little (row, key, range)locks, and then tries to escalate them to a big
(table) lock. But it fails to get the table lock, and rolls back the work.
As described here:

Resolving blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;323630

Although the remedial measures listed in that KB omit the most obvious fix:
to lock the whole damn table before you run the query. You are most
probably trying to escalate locks on the destination table. So to fix this
instruct SQL Server to obtain an exclusive lock on the destination table for
the duration of the load. No other session will be able to use the table
until you are done loading it.

Adding with(tablockx) to your destination table will cause the insert to
wait until it can obtain exclusive access to the destination table before it
starts loading data. It's a whole lot better to wait for the table lock at
the beginning than after the insert already owns, say, 4000 key locks. If
the INSERT already owns locks and tries to escalate it may be unable to
escalate because another session owns an incompatible lock and is itself
waiting on one of the locks the INSERT already owns.

David

 

[adi]

Thanks a lot David.

Yes I had the issue because there was a FTI on the destination table
and when I last checked it escalated to 2 million locks! and that froze
up the whole DB.

I tried the tablockx for the 3 hundred 50 thousand rows and it
completed in 10 mins (which is good)

I am now trying out the whole 4 millions, should probably take 2 hrs.

I will Keep everyone posted.

 

[adi]

Having used this approach I still want to improve its performance.
My DBA says that splitting the insert statement into smaller chunks
will be very helpful - that is insert n number of records at a time.
so if I have 1 million rows to insert, he says its good to insert 50k
at a time.

Can someone help me on how to split the below insert to do that?

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable.

 

[William \(Bill\) Vaughn]

How do you spell "cuckoo"? I would talk to another DBA. To move a million
rows (or even a few hundred), I would use BCP or DTS. ADO.NET 2.0 includes
the SqlBulkCopy class and it can import data from anywhere. Even if you're
moving data to Oracle you can still use BCP or DTS.
If you really MUST use ADO (which was never designed for bulk operations),
building sets of INSERT statements is really a function of what works best.
I wrote a routine (as a demo) years ago that increased the "block size"
incrementally until it took longer to INSERT than to fetch the rows for the
next block. The optimal number (at the time) was about 200 rows.

hth


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[adi]

Yes, but I am not using ADO here, I only call it from C#, the whole
insert sits in a stored procedure which I call from the .NET

I did try the DTS approach, it had the same problem of lock escalation
(because the destination table had a FTI and it had millions of rows
coming in)

thanks

 

[William \(Bill\) Vaughn]

The trick is to pull the rows into the server into a disconnected temporary
table that's filtered and integrated server-side into the live data post
BCP. This way you don't have locks or the TL to worry about.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________