|
OK, tried everything..
1. I am using "scope_identity()"
instead of @@IDENTITY - I knew this was not the problem as it returned the
correct identity before, however I am using it now, as it sounds like a
better option.
2. Checked the value of @bError and its
definately set to 0 at the end, so a COMMIT should be fired.
Checked the lock processes, but nothing out of
the ordinary.
3. Removed the full-text catalog indexing from
the SP
Still getting the same problem. Funny thing is
though, if I execute the same code through the SQL Query Analyzer it executes
fine without any delay...
I have just now noticed
though, that the insert is immediate i.e. as soon as I post the page the record
is inserted, however the select statement that should return the new record to
the client again does not retrieve it.
hhmmm, locking or transaction
issue??
Tried something new, I removed the
TRANSACTION statements,
however still the same, so it's not a transaction issue. All thats left is a
locking issue I beleive, but where and how??
CREATE PROCEDURE spForumThreadInsert
(
@categoryID INTEGER,
@header VARCHAR(200),
@threadBody VARCHAR(8000),
@someoneID INTEGER,
@startDate SMALLDATETIME = NULL,
@expiryDate SMALLDATETIME =
NULL,
@newThreadID INTEGER = NULL OUTPUT,
@bApprovalRequired
BIT = NULL OUTPUT,
@bError BIT = NULL OUTPUT
)
AS
SET @bError = 0
--BEGIN TRANSACTION
DECLARE @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 = SCOPE_IDENTITY()
-- Update category statistics, i.e. thread
count
UPDATE tblForumCategory
SET threadCount
= threadCount + 1
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
PRINT @bError
*/
IF @bError = 0
BEGIN
-- Update count of records added to full-text search
tables
UPDATE tblSystemVariable
SET variableInteger
= variableInteger + 1,
variableDate =
GETDATE()
WHERE (variableName =
'fullTextCount')
END
GO
----- 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/
---
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/
|
