April 21, 2010 at 8:37 am
I have a stored procedure that is causing exceptions to be thrown regularly in my ASP.NET app.
The stored proc is basically inserting into 2 tables, with the inserts wrapped in a transaction. This is the only stored proc that is making inserts into this database, so I am assuming that this stored proc is the problem, and maybe the stored proc design is flawed.
Is there a better way to make these inserts without getting deadlocks? Here is my stored proc code:
ALTER PROCEDURE [dbo].[s_StatsInsert]
(
@StatsXML nvarchar(4000),
@Result int output
)
AS
BEGIN
DECLARE @DocHandle int, @PageId int, @Error int
EXEC sp_xml_preparedocument @DocHandle output, @StatsXML
BEGIN TRAN
INSERT INTO Page([Date], SiteName, PageName, RawUrl, ClientIP, SessionID, Username, UserAgent, Referer)
SELECT P.[Date], P.SiteName,
CASE P.PageName
WHEN 'handlers/glossary.ashx' THEN 'document.aspx'
WHEN 'handlers/sectionlog.ashx' THEN 'document.aspx'
ELSE P.PageName
END,
P.RawUrl, P.ClientIP, P.SessionID, P.Username, P.UserAgent, P.Referer
FROM OPENXML(@DocHandle, '/Page', 2)
WITH (
[Date] datetime,
SiteName varchar(50),
PageName varchar(50),
RawUrl varchar(256),
ClientIP varchar(64),
SessionID varchar(40),
Username varchar(50),
UserAgent varchar(256),
Referer varchar(256)
) P
SET @Error = @@ERROR
SET @PageId = SCOPE_IDENTITY()
IF @Error = 0 BEGIN
INSERT INTO PageData(PageId, [Name], Value)
SELECT @PageId, D.[Name], D.Value
FROM OPENXML(@DocHandle, '/Page/Data', 2)
WITH (
[Name] nvarchar(256),
Value nvarchar(256)
) D
END
SET @Error = @@ERROR
IF @Error = 0 BEGIN
COMMIT TRAN
SET @Result = 0
END
ELSE BEGIN
ROLLBACK TRAN
SET @RESULT = -1
END
EXEC sp_xml_removedocument @DocHandle
END
Thanks for looking.
Joel
April 21, 2010 at 9:21 am
How big is the XML you are passing in? I've seen issues where if this is large, then it will take a while to parse through it. I've found better performance putting it in a table variable first and then using that in the inserts. Also, you should use a try catch block instead of what your using. Lastly, in the catch part of the try/catch, you can check for deadlocks and then retry. See this article on MSDN about how to do this
http://msdn.microsoft.com/en-us/library/aa175791(SQL.80).aspx
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 21, 2010 at 10:05 am
My XML is aroung 600 chars long which results in 1 record being inserted into the first table and 4 or 5 records being inserted into the second table. I will try using a temp table so that I am not inserting directly from the XML, as you have suggested.
... but a question on locking. The first insert statement causes a lock on the first table, when would this lock get released? I assumed that because I put both inserts into transaction, then the lock on the first table would not be released until the transaction is committed. Is this how locking works? maybe my understanding is wrong.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply