Re: Bulk Insert Data in Millions - Lock Issue

Just a thought, its too early :). What would happen if he used NOLOCK
option? He doesnt really need locks around the place does he?

MC


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns96ECF2B98CD3FYazorman@xxxxxxxxxxxx
> adi (Adityanad@xxxxxxxxx) writes:
>> I am inserting data from one table to another on the same DB. The
>> insert is pretty simple as in:
>>
>> 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..)
>
> There are a couple of techniques. But for best performance, you need
> a clustered index in the table. This does not have to be unqiue though.
> Say that you have a date column in the table that has a decent
> distribution.
> Create a clustered index on tbat column:
>
> CREATE CLUSTERED INDEX ON temptable(datecol)
>
> Then you can do:
>
> DECLARE @this_date datetime,
> @next_date datetim
> SELECT @this_date = MIN(datecol) FROM temptable
> WHILE @this_date <= (SELECT datecol FROM temptable)
> BEGIN
> SELECT @next_date = dateadd(MONTH, 1, @this_date)
> INSERT dataable (...)
> SELECT ...
> FROM temptable
> WHERE datecol >= @this_date
> AND daetcol < @next_date
> SELECT @this_date = @next_date
> END
>
> It's important that the index is clustered to minimize seek times.
> In the example I used month, but this can be changed as you see fit.
>
> If you don't have a datetime column, maybe there is some id column or
> similar. But you could even use a value like max temperature, as long
> there is a decent disitribution.
>
> You can also use SET ROWCOUNT to TOP to batch, but it's messier to
> keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
> with could have poor performance.)
>
>> 2)to use waitfor - which I did but did not fix the error.
>
> I will have to admit that I don't see the point with WAITFOR here.
>
>> 3)use bulk insert (in t-sql) - I dont know how to do this?
>
> This requires you to first unload the temptable to file with BCP, and
> then load it to the target table with BCP or BULK INSERT. I skip
> examples, as this would be a last resort for me.
>
> Then again, your also try:
>
> INSERT datatable (...) WITH (TABLOCKX)
> SELECT ...
>
> I have never tried it, but I expect it to lead to a single table lock
> in the table, which which address the locking issue.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>


.

Relevant Pages

  • Re: How does CREATE INDEX impact current users?
    ... take an X lock on the table, thus preventing any access to the table at all. ... This problem is alleviated in SQL Server 2005 with the concept of online ... Creating a clustered index does not copy the table per se - the heap ...
    (microsoft.public.sqlserver.server)
  • Re: Optimizing massive update to large table
    ... Direct FIBRE channel connection to a 3.5 TB Apple ... I don't think you have a clustered index on the ... Consider adding a table lock hint to the update to keep SQL Server from ...
    (microsoft.public.sqlserver.server)
  • Re: using clustered index to optimize inserts ...
    ... I will try to explain locking in terms of Sybase docs... ... Allpages Locking: Allpages locking locks both data pages and index ... the data page is locked with an exclusive lock. ... Clustered Index: The datarows will be arranged as per the clustered ...
    (comp.databases.sybase)
  • Re: deadlock problem
    ... in the log it throws an exculsive lock on the select statement ... retry following a deadlock ... CleanCnt:1 Mode: X Flags: 0x0 ... Update table T1 where it goes for Clustered Index Seek. ...
    (comp.databases.ms-sqlserver)
  • Re: deadlock problem
    ... in the log it throws an exculsive lock on the select statement ... the fileds of the clustered index. ... Scans are notorious for causing deadlocks with row-level locking. ... Session 1: ...
    (comp.databases.ms-sqlserver)
Loading