December 1, 2008 at 2:26 am
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
December 1, 2008 at 4:18 am
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
December 1, 2008 at 4:27 am
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
December 1, 2008 at 4:33 am
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
December 1, 2008 at 8:54 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy