Need help with poorly performing SPROC

SQL Apprentice Question
I have a SPROC that gradually slows to a crawl. It processes roughly
38,000 records from a staging table, makes some alterations to data,
and subsequently calls other SPROCS to either insert or update
production data based on what is in the staging table.

When I initially created it, I opened a cursor for the staging table
and went through all of the records. Memory use would gradually
increase to debilitating levels. In an effort to improve performance, I
used a table variable instead of the cursor, and the performance has
improved only slightly. The memory use appears to be stable, but it
looks as though processor usage increases over time (which doesn't
really make sense to me).


When it first starts to run, it is processing about 130 records per
minute. 20 minutes in, it is processing less than 40 records a minute.


For clarity, I am including the SQL here:


CREATE PROCEDURE moveIndividualDataToProduction
@iReturnCode int OUTPUT
AS


--Gett all records in staging, calling wbospsiIndividual to insert all
new records, and/or wbospsuIndividual to update records
DECLARE --vars for the records from IndividualStaging
@iError int,
@iIndividualId int,
@iSiteId int,
@chLanguageCode nchar (4),



--vars to hold existing data from Individual


@oldiIndividualId int,
@oldiSiteId int,
@oldchLanguageCode nchar (4),
@oldvchAssignedId nvarchar (255),
@oldvchSalutation nvarchar (255),
@oldvchFirstName nvarchar (255),



--Need this one to determine the useraccesslevel for ocpuser
@onyxsource nvarchar(15),


--Var for nssg id
@nssgid int,


--vars for xp_smtpmail
@mailmessage varchar(255),
@mailsubject varchar(255),


--vars for OCPUser Insert
@OchOCPUserId nchar(255),
@OiSiteId int,
@OiSystemId int,
@OiTableId int,
@OvchUserAccessLevel nvarchar(25),
@OvchPassword nvarchar(255),



--Need this to store what the next ID will be
@NextID int,


--Store the new individual ID
@newid int,


--Local vars for table variable
@count int,
@iRow int


Select @NextID = iLastId + 1 from LastId where chIdType = 'ocpuser'


DECLARE @IndividualTbl TABLE(
RowID INT IDENTITY(1, 1),
TViIndividualId int,
TViSiteId int,
TVchLanguageCode nchar (4),
TVvchAssignedId nvarchar (255),
TVvchSalutation nvarchar (255),
TVvchFirstName nvarchar (255),
)


--Fill the table variable
INSERT @IndividualTbl
SELECT *
FROM IndividualStaging


SET @count = @@ROWCOUNT
SET @iRow = 1
WHILE @iRow <= @count
BEGIN
SELECT @iIndividualId = TViIndividualId,
@iSiteId = TViSiteId,
@chLanguageCode = TVchLanguageCode,
@vchAssignedId = TVvchAssignedId,
@vchSalutation = TVvchSalutation,
@vchFirstName = TVvchFirstName,
@vchMiddleName = TVvchMiddleName,
@vchLastName = TVvchLastName,

FROM @IndividualTbl
WHERE RowID = @iRow


--Check to see if this is new
IF NOT EXISTS (select vchUser10 from Individual where vchUser10 =
@vchUser10)
BEGIN
--First, we need to add an ID if there is none, and email the person
with the ID
If Len(@vchUser7) < 1 or @vchUser7 IS NULL
Begin
Exec getNewID @newid OUT
Set @vchUser7 = @newid
--We need to email the user with the new ID
Select @mailmessage = N'Blah''
Select @mailsubject = N'Blah'


--exec master..xp_sendmail @recipients='m...@mail.com,@message =
@mailmessage ,@subject = mailsubject
exec master.dbo.xp_smtp_sendmail
@FROM = N'm...@mail.com',
@FROM_NAME = N'SQL Automation Email',


@replyto = N'm...@mail.com',
@TO = N'm...@mail.com',
--@TO = @vchEmailAddress,
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = @mailsubject,


@type = N'text/plain',
@message = @mailmessage,


@messagefile= N'',
@attachment = N'',


@attachments= N'',
@codepage = 0,


@timeout = 10000,
@server = N'1.1.1.1'
End


--Call the insert SPROC
exec wbospsiIndividual
@iSiteId,
@iIndividualId OUT,
@chLanguageCode,
@vchAssignedId,
@vchSalutation,
@vchFirstName,
@vchMiddleName,



--Get everything ready to insert new OCPUser record
Select @onyxsource = onyxsource from ldapusers where entryuuid =
@vchUser10
Set @OchOCPUserId = N'U-D' + REPLICATE('0',6 - LEN(CAST(@NextID as
nvarchar(6)))) + CAST(@NextID as nvarchar(6))
Set @OiSiteId = 1
Set @OiSystemId = @iIndividualId
Set @OiTableId = 1
If @onyxsource = 'kbt_usa'
BEGIN
Set @OvchUserAccessLevel = 'OCPLead'
END
Else
BEGIN
Set @OvchUserAccessLevel = 'OCPCust'
END
select @OvchPassword = dbo.cfncEncryptPassword(@OchOCPUserId,'junk')
Set @OchLanguageCode = N'ENG '
Set @OvchUser1 = N'BUS'
Set @OvchUser2 = LEFT(@vchFirstName + N' ' + @vchLastName, 255)
Set @OvchUser3 = @vchEmailAddress
Set @OvchUser4 = NULL
Set @OvchUser5 = NULL
Set @OvchUser6 = NULL
Set @OvchUser7 = NULL
Set @OvchUser8 = NULL
Set @OvchUser9 = NULL
Set @OvchUser10 = NULL
Set @OchInsertBy = @chInsertBy
Set @OdtInsertDate = @dtInsertDate
Set @OchUpdateBy = @chUpdateBy
Set @OdtUpdateDate = @dtUpdateDate
Set @OtiRecordStatus = @tiRecordStatus


Set @NextID = @NextID + 1


Insert into OCPUser values(
@OchOCPUserId,
@OiSiteId,
@OiSystemId,
@OiTableId,
@OvchUserAccessLevel,
@OvchPassword,
@OchLanguageCode,
@OvchUser1,
@OvchUser2,
@OvchUser3,
@OvchUser4,
)
END


--Otherwise, this is an update
ELSE
BEGIN
--Grab the existing record values that won't change in LDAP


select @oldiIndividualId = iIndividualId ,
@oldiSiteId = iSiteId ,
@oldchLanguageCode = chLanguageCode,
@oldvchAssignedId = vchAssignedId,
@oldvchSalutation = vchSalutation,
@oldvchSuffix = vchSuffix,
@oldvchURL = vchURL,
@oldchGender = chGender,
@oldiUserTypeId = iUserTypeId,
@oldiUserSubTypeId = iUserSubTypeId,
@oldchTitleCode = chTitleCode,
@oldvchTitleDesc = vchTitleDesc,
@oldvchDepartmentDesc = vchDepartmentDesc,
@oldiPhoneTypeId = iPhoneTypeId,

from individual where vchUser10 = @vchUser10


--Call the update SPROC
exec wbospsiIndividual
--Keep values from old record - these don't change in LDAP
@oldiSiteId,
@oldiIndividualId,
@oldchLanguageCode,
@oldvchAssignedId,

--Get these values from LDAP, they could have changed
@vchUser4,
--Keep values from old record - these don't change in LDAP
@oldvchUser5,
--Get these values from LDAP, they could have changed
@vchUser6,
--Keep values from old record - these don't change in LDAP
@oldvchUser7,
--Get these values from LDAP, they could have changed
@vchUser8,
@vchUser9,
@vchUser10,
@chUpdateBy,
@dtUpdateDate,
0,
@tiRecordStatus


--Need to update the email address in the OCPUser record - vchUser3
update ocpuser set vchUser3 = @vchEmailAddress where iSystemId =
@oldiIndividualId


END
SET @iRow = @iRow + 1
END


Update LastId Set iLastId = @NextID - 1 where chIdType = 'ocpuser'


--CLOSE Individual_Cursor
--DEALLOCATE Individual_Cursor


SELECT @iError = @@ERROR
IF @iError <> 0
BEGIN
SET @iReturnCode = 1
RETURN @iReturnCode
END
GO



Celko Answers
I am trying to think of a nice way to say this, but you did not get
anything right.

You do not know that rows are not anything like records, that SQL is a
set-oriented language, how to name data elements with ISO-11179 rules
(you actually put data types in names and used camelCase in SQL!), you
used proprietary features to assure this is hard to maintain and port,
etc.


What you have done is mimic a 1950's magnetic tape file system, right
down to the terminology, in SQL destroying all the advantages of RDBMS.


This procedure should be an UPDATE statement and an INSERT INTO
statement and that is about all. Some day SQL Server might have the
SQL-99 MERGE statement and you can do in one statement.


No temp tables, no loops, no cursors, etc.


Since you did not bother to post DDL, it is hard to guess what is
supposed to happen.