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

John Reed Mon, 03 Feb 2003 14:19:42 -0800

Title: Message

Hmmm...

OK, lets look at the CF code side for a minute. I'm assuming you'd have to be using cfstoredproc as your stored proc has output params?

"however the select statement that should return the new record to the client again does not retrieve it"

Do you have the cfstoredproc's cfprocparam set to something like this:

<cfprocparam

type="out"

variable="myVar" (careful not to use value="myVar", although I would expect the compiler to pick that one up.)

cfsqltype="CF_SQL_INTEGER"

dbvarname="@newThreadID"

null="yes"

The new thread_id is <cfoutput>#VARIABLES.myVar#</cfoutput>!

I wouldn't mind changing this part of your proc just to be sure of whats returned (if anything):

IF NOT (@@ERROR = 0)
BEGIN
SET @bError = 1
END
SET @newThreadID = SCOPE_IDENTITY()

Changed to:

IF @@ERROR <> 0
BEGIN
SET @newThreadID = -1

RETURN @@ERROR

END
ELSE

BEGIN

SET @newThreadID = SCOPE_IDENTITY()

END

The reason I want to change this is if an error did occur above the next part of your proc would still execute and not return an identity value. To use the above code you need to enable the return code in the cfstoredproc and catch it using using the #cfstoredproc.statuscode#.

Cheers,

John

PS. This is nothing to do with the problem your having, but be carefull with your rows sizes (Bytes per row). SQL Server can hold 8060 bytes the rest that makes the 8192 bytes is SQL's overhead (header information). If a user enters a thread that has a combined column size greater than 8060 bytes, your insert will fall over.

-----Original Message-----
From: Taco Fleur [mailto:[EMAIL PROTECTED]]
Sent: Monday, 3 February 2003 16:49
To: CFAussie Mailing List
Subject: [cfaussie] RE: Slow insert with SP ???

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

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