Very slow Inserts when using SQLCommand.ExecuteNonQuery from .NET 2K5

Question

Very slow Inserts when using SQLCommand.ExecuteNonQuery from .NET 2K5

puneet shadija

SSC-Addicted

Points: 484
More actions
December 1, 2008 at 2:26 am

#127296

Hi All,
When I am trying to make lots 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why the app lock? What's that for, and could that be the cause? If lots of other procs are alse using applock, it could take a while for this one to obtain the lock.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

puneet shadija SSC-Addicted Points: 484 More actions · Answer 2

since we have many threads, which may concurrently try to insert a record to this table, thus I have used this lock to prevent this from happening

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

puneet shadija (12/1/2008)
since we have many threads, which may concurrently try to insert a record to this table, thus I have used this lock to prevent this from happening

Why? What's wrong with letting SQL manage the locks itself?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004223 More actions · Answer 4

puneet shadija (12/1/2008)
since we have many threads, which may concurrently try to insert a record to this table, thus I have used this lock to prevent this from happening

You just don't need to do that. If the table has an IDENTITY column on it, you can use SCOPE_IDENTITY() to find out which row you just inserted for any given session. You can also use the OUTPUT clause to return a result set of what you just inserted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)