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