SQL Server Performance Forum – Threads Archive
Parallel Data Loads
Hi, I’m trying to setup a DTS package that will have two threads performing BULK INSERTS into the same table. I’ve got the DTS package doing the threading part. But each of the bulk inserts wait behind the other one until it has finished – instead of running in parallel. MS has this document: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_1968.asp Which suggests using TABLOCK on the bulk insert – this I don’t understand because this means each thread would intentionally try to take exclusive locks (????). Has anyone managed to get parallel bulk inserts to work? If so please let me know how! ThanksSimon
Not sure if it helps, but you know this one?
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
TABLOCK specifies that a bulk update table-level lock is taken for the duration of the bulk copy. Using TABLOCK can improve performance of the bulk copy operation due to reduced lock contention on the table. BOL specifies to use:
BULK INSERT pubs..authors2 FROM ‘c:authors.txt’
WITH (
DATAFILETYPE = ‘char’,
FIELDTERMINATOR = ‘,’,
TABLOCK
) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks Frank. From that article:
quote:
When the target table has no indexes and TABLOCK is specified, you can execute the multiple bulk load commands concurrently. These concurrent bulk loads use a Bulk Update (BU) lock at the table level that inserts the data without blocking other sessions. If you use TABLOCK with no index, the bulk-logged optimizations are also available. It does not matter if the table is empty or full. This is the fastest way to bulk load data. The BU lock, however, conflicts with regular share locks and exclusive locks. If TABLOCK is not specified, you can still execute multiple bulk load commands concurrently, however, each of these commands uses regular locking, like the locking used by Transact-SQL Insert, and the bulk-logged optimizations are not available. • Single nonclustered index or a clustered index When the target table has an index, concurrent bulk loading with BU lock is not possible with TABLOCK.
When the target table has no indexes and TABLOCK is specified, you can execute the multiple bulk load commands concurrently. These concurrent bulk loads use a Bulk Update (BU) lock at the table level that inserts the data without blocking other sessions. If you use TABLOCK with no index, the bulk-logged optimizations are also available. It does not matter if the table is empty or full. This is the fastest way to bulk load data. The BU lock, however, conflicts with regular share locks and exclusive locks. If TABLOCK is not specified, you can still execute multiple bulk load commands concurrently, however, each of these commands uses regular locking, like the locking used by Transact-SQL Insert, and the bulk-logged optimizations are not available. • Single nonclustered index or a clustered index When the target table has an index, concurrent bulk loading with BU lock is not possible with TABLOCK.
It seems TABLOCK is not exclusive to other TABLOCKs providing that there is not a clustered index on the table. Clever MS!
]]>