VB.NET

Welcome to Dream.In.Code
	Become a VB.NET Expert! Join 244,219 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,411 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a VB.NET Expert

Very slow inserts using SQLCommand.ExecuteNonQuery

Very slow inserts using SQLCommand.ExecuteNonQuery

Options

puneetshadija	30 Nov, 2008 - 11:07 PM Post #1
New D.I.C Head Joined: 30 Nov, 2008 Posts: 1	Hi All, When I am trying to make loads of Inserts into my database ... it takes lots of time to insert using SqlCommand.ExecuteNonQuery(). Even though I have created an SP as following: CODE ALTER PROCEDURE myProc ( @param1 varchar(50), @param2 int, @param3 decimal(18,0), @param4 decimal(18,0) ) AS BEGIN BEGIN TRAN DECLARE @result int; exec @result = sp_getapplock = 'Lock_Id', @LockMode = 'Exclusive'; IF @result = -3 BEGIN ROLLBACK TRANSACTION END ELSE BEGIN INSERT INTO myTable values(@param1, @param2, @param3, @param4) exec @result = sp_releaseapplock @Resources = 'LockId' COMMIT TRANSACTION END END END I am not sure, if the problem is with the above SP or any other reason coz of which, it takes such a long time to insert. Plz suggest !!! Thanks
	This Post Was Helpful!

Register for Free to Make These Ads Go Away!

jens	RE: Very Slow Inserts Using SQLCommand.ExecuteNonQuery 1 Dec, 2008 - 01:14 AM Post #2
D.I.C Regular Joined: 9 May, 2008 Posts: 263 Thanked: 18 times Dream Kudos: 475 My Contributions	Maybe you should look at SqlBulkCopy as I was told to do. Besides this, not knowing about stored procedures I still wonder: Shouldn't exec @result = sp_releaseapplock @Resources = 'LockId' be outside the COMMIT TRANSACTION? /Jens
	This Post Was Helpful!

magicmonkey

RE: Very Slow Inserts Using SQLCommand.ExecuteNonQuery
1 Dec, 2008 - 06:10 AM

Post #3

D.I.C Regular

Joined: 12 Sep, 2008
Posts: 484

Windows

Thanked: 93 times
My Contributions

The reason that your inserts are so slow is because that proc is full of errors, and must be throwing an exception every time you call it. I tried to clean it up, I am not sure why you want to create the application lock for what you are doing thou.

sql


ALTER PROCEDURE myProc
(
@param1 varchar(50),
@param2 int,
@param3 decimal(18,0),
@param4 decimal(18,0)
)
AS 

BEGIN TRAN;
DECLARE @result int;
exec @result = sp_getapplock @Resource = 'Lock_Id', @LockMode = 'Exclusive';
IF @result = -3
  BEGIN
    ROLLBACK TRANSACTION;
  END
ELSE
  BEGIN
    INSERT INTO myTable values(@param1, @param2, @param3, @param4)
    exec @result = sp_releaseapplock @Resource = 'Lock_Id';
    COMMIT TRANSACTION;
  END

I see no need for the applock why not just run the insert?

sql


ALTER PROCEDURE myProc
(
@param1 varchar(50),
@param2 int,
@param3 decimal(18,0),
@param4 decimal(18,0)
)
AS 

INSERT INTO myTable values(@param1, @param2, @param3, @param4)