----- Original Message -----
Sent: Monday, February 03, 2003 8:49
AM
Subject: [cfaussie] RE: Slow insert with
SP ???
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/
---