VB.NET School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Welcome to Dream.In.Code
Become a VB.NET Expert!

Join 300,480 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,764 people online right now. Registration is fast and FREE... Join Now!




Very slow inserts using SQLCommand.ExecuteNonQuery

 

Very slow inserts using SQLCommand.ExecuteNonQuery

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

User is offlineProfile CardPM
+Quote Post


jens

RE: Very Slow Inserts Using SQLCommand.ExecuteNonQuery

1 Dec, 2008 - 01:14 AM
Post #2

D.I.C Regular
Group Icon

Joined: 9 May, 2008
Posts: 263



Thanked: 20 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
User is offlineProfile CardPM
+Quote Post

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



Thanked: 97 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)




User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/8/09 03:47AM

Live VB.NET Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

VB.NET Tutorials

Reference Sheets

VB.NET Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month