[cfaussie] RE: Slow insert with SP ???

John Reed Sun, 02 Feb 2003 14:49:40 -0800

Title: Message

Hi Taco,

I can think of a few things to look for with your stored proc.

1. Try not to use @@identity to get the new identity column value. Use "scope_identity()" as it will return the value created in the executing procedure, @@identity gets set globally in the db.

example: SET @newThreadID = SCOPE_IDENTITY()

** See below for more details (from BOL).

2. Run your sp code through the debugger to ensure the Boolean values are being set as expected. If there is a problem here that has not been identified you maybe getting locking issues (from transactions) that are taking time to resolve. Go to ent. mgr and check the lock / process id for problems.

3. This is the one I think is the most likely problem:

    IF @bError = 0
        BEGIN
             -- Reindex full-text catalog if required
             EXECUTE spFullTextIncremental '1'
        END

Try temporarily removing this from the procedure. Reindexing the full text catalogue could be taking a while to complete.

Have you upgraded SQL Server with any service packs by any chance? I have heard a few other dba's mention problems with SQL full text search catalogues becoming corrupt. Just a thought anyway.

** >From BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

There is more information in BOL, search for SCOPE_IDENTITY not SCOPE_IDENTITY()

Hope that helps,

John

-----Original Message-----
From: Taco Fleur [mailto:[EMAIL PROTECTED]]
Sent: Friday, 31 January 2003 20:50
To: CFAussie Mailing List
Subject: [cfaussie] Slow insert with SP ???

Hi all,

i'm doing this insert (see attached code) after the insert I do a immediate re-direct to the inserted "Thread" however 2/10 the thread has not arrived in the db yet! I have to refresh the page several times and it somethimes takes more than 25 seconds for it to show up!

I have honestly never experienced anything like this with SQL Server, it's always pretty darn fast...

Am I missing something simple? Anyone??

CREATE PROCEDURE spForumThreadInsert
(
@categoryID INTEGER,
@header VARCHAR(200),
@threadBody VARCHAR(8000),
@someoneID INTEGER,
@startDate SMALLDATETIME = NULL,
@expiryDate SMALLDATETIME = NULL,
@newThreadID INTEGER = NULL OUTPUT
)
AS

DECLARE @bError BIT
SET @bError = 0

BEGIN TRANSACTION

DECLARE @bApprovalRequired BIT, @bApproved BIT

SET @bApprovalRequired = (SELECT approvalRequired FROM tblForumCategory WHERE (pkCategoryID = @categoryID))

IF @bApprovalRequired = 0
BEGIN
  SET @bApproved = 1
END
ELSE
BEGIN
  SET @bApproved = 0
END

IF @startDate IS NULL
  SET @startDate = GETDATE()

INSERT INTO tblForumThread
   (fkCategoryID,
   fkSomeoneID,
   threadHeader,
   threadBody,
   fkStatusID,
   startDate,
   expiryDate)
VALUES (@categoryID,
   @someoneID,
   @header,
   @threadBody,
   @bApproved,
   @startDate,
   @expiryDate)
-- Check if there are any errors
IF NOT (@@ERROR = 0)
BEGIN
  SET @bError = 1
END
SET @newThreadID = @@IDENTITY

-- Update category statistics, i.e. thread count, last post by etc.
UPDATE tblForumCategory
SET  threadCount = threadCount + 1,
   fkLastPostByID = @someoneID,
   dateStamp = GETDATE()
WHERE (pkCategoryID = @categoryID)
-- Check if there are any errors
IF NOT (@@ERROR = 0)
BEGIN
  SET @bError = 1
END

IF @bError = 1
BEGIN
-- Rollback transaction if there was an error
ROLLBACK TRANSACTION
END
ELSE
BEGIN
-- Commit transaction if there was no error
COMMIT TRANSACTION
END

IF @bError = 0
BEGIN
-- Reindex full-text catalog if required
EXECUTE spFullTextIncremental '1'
END
GO

I wrote a little procedure that keeps redirecting the page untill the recordcount NEQ 0, but that is of course not the solution.

TIA

Taco Fleur
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

[cfaussie] RE: Slow insert with SP ???

Reply via email to