----- 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/
---