Posted by Erland Sommarskog on 06/04/05 23:50

[posted and mailed, please reply in news]

(mahajan.sanjeev@gmail.com) writes:
> I am using a SQLTransaction to insert records into a table. At one
> time, there are 5000 or more records to be inserted one by one. It
> takes some 20-25 mins for the entire process to run.
>
> Another application accesses the same table.
>
> As long as the insert process within the transaction isn't completed,
> the second application is not getting any response from the server. I
> even tried to run a SELECT on the table in SQL Query Analyzer while the
> insert process was running and it also did not respond till the time
> the insert process finished!
>
> Is this normal that a transaction is locking up a table? How do I
> overcome it? I am using IsolationLevel.ReadUncommitted for the
> transaction.

Which isolation level you use for the INSERT, does not really matter here.
If another process says "SELECT * FROM tbl" when the INSERT process is
running, that process will be blocked, since it cannot read the newly
inserted but uncommitted. Unless, that is, the SELECT processes uses the
READ UNCOMMITTED isolation level. However, one should be very careful
with using dirty reads. If you don't understand the implications of
dirty reads, you should not play with them.

20-25 minutes for inserting 5000 rows is an awful lot of time. Is there
a trigger on the table? Inserting one-by-one is never effective, but
250 ms seconds per row is extreme.

One thing you could consider is to build an XML string from your
data, and then pass that string to a stored procedure which uses
OPENXML to shred this string. Then you can insert all 5000 rows in
one go. We have been using this in our application, to improve performance
in places where the GUI needs to store a lot of data in the database.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]