July 11, 2008 at 11:01 am
This morning SQL Server job ran and
one of the steps (DTS calling stored procedure IMPORT_Clients)
caused CPU spike to 100%.
It happened at 6:30 AM.
Now it's 12:40 PM and in Task manager "sqlservr.exe" CPU is still 100%.
I went to Enterprise Manager,
Current Activity,
Locks / Process ID
and for spid 61 there are 85 items.
Almost all records there have Object "tempdb".
Object Lock Type Mode Status Owner Index
-------------------------------------------------------------
tempdb EXT X GRANT Xact ##lockinfo55
........
tempdb.dbo.#TMP_CLIENTS_PROCESSING TAB X
vdImport DB
vsNiad DB
.....
vsNiad.dbo.ClientAddress PAG UI PK_Clients
July 11, 2008 at 2:32 pm
Is the job still running?
What does that proc do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2008 at 3:58 pm
What are you hoping to get from us?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 11, 2008 at 4:43 pm
rbarryyoung (7/11/2008)
What are you hoping to get from us?
What Barry means is that you've given us nothing but symptoms. The problem is going to be what appears in the stored procedure. I recommend you post the stored procedure... and the related table CREATE statements. If the code isn't well documented, please either write out a good description of what the code is supposed to do or add some comments into the code for each section identifying what it's supposed to be doing... not what it does. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2008 at 9:36 am
Code is below.
Job slows down at
"Clients...Start-Batch Updates" point, CPU spikes to 100%,
after 11 hours it finally moves further and the job finishes.
CREATE PROCEDURE [dbo].[IMPORT_Clients]
@Appid INTEGER
AS
SET NOCOUNT ON
-- declare all variables!
DECLARE @iReturnCode INTEGER,
@iNextRowId INTEGER,
@iCurrentRowId INTEGER,
@iLoopControl INTEGER,
@FieldErrors INTEGER,
@iRowCount INTEGER,
@iInsertIND INTEGER
--Declare Import data
DECLARE @numAddressesINTEGER,
@numPhonesINTEGER,
@numEmailsINTEGER
DECLARE @a INTEGER
DECLARE @intErrorCode INTEGER
DECLARE @sql varchar(2000)
DECLARE @RowByRow INTEGER,
@BatchInsertStatus INTEGER,
@BatchUpdateStatus INTEGER,
@RecordsCount INTEGER
SET @RecordsCount = 0
DECLARE
@FirmID INT,
@ClientId INT,
@ClientCode VARCHAR(64),
@Phone VARCHAR(50),
@Phone1 VARCHAR(50),
@Phone2 VARCHAR(50),
@Phone3 VARCHAR(50),
@Phone4 VARCHAR(50),
@Address VARCHAR(255),
@Address1 VARCHAR(255),
@Address2 VARCHAR(255),
@Address3 VARCHAR(255),
@Address4 VARCHAR(255),
@Address5 VARCHAR(255),
@Address6 VARCHAR(255),
@Address7 VARCHAR(255),
@Address8 VARCHAR(255),
@Email VARCHAR(50),
@Email1 VARCHAR(50),
@Email2 VARCHAR(50),
@Email3 VARCHAR(50),
@Email4 VARCHAR(50)
SELECT @intErrorCode = @@Error
INSERT INTO vsImport..import_log SELECT 'Clients...Start','ImportClients',0,@intErrorCode,0,0 ,getdate()
--Create Temporary Table
--drop table #TMP_CLIENTS_PROCESSING
CREATE TABLE #TMP_CLIENTS_PROCESSING(
RecordIdINTEGER,
ApplicationId INTEGER NULL,
ClientCode varchar(64) NULL,
LastName varchar(64) NULL,
FirstName varchar(64) NULL,
RRCode varchar(64) NULL,
InvestorTypeId INTEGER NULL,
PostalCode VARCHAR(50) NULL,
ResidenceShortNamevarchar(50) NULL,
CountryCode varchar(64) NULL,
SINvarchar(10) NULL,
SpouseName varchar(100) NULL,
SpouseSin varchar(10) NULL,
JointCLientAccountId INTEGER NULL,
City varchar(50) NULL,
LanguageId INTEGER NULL,
Phone1 varchar(50) null,
Phone2 varchar(50) null,
Phone3 varchar(50) null,
Phone4 varchar(50) null,
Address1 varchar(255) null,
Address2 varchar(255) null,
Address3 varchar(255) null,
Address4 varchar(255) null,
Address5 varchar(255) null,
Address6 varchar(255) null,
Address7 varchar(255) null,
Address8 varchar(255) null,
Email1 varchar(50) null,
Email2 varchar(50) null,
Email3 varchar(50) null,
Email4 varchar(50) null,
Status INTEGER null,
-- RETRIEVED(Look UP) DATA
FirmId INTEGER NULL,
CountryId INTEGER NULL,
RRCodeId INTEGER NULL,
ResidenceId INTEGER NULL,
ClientId INTEGER null,
ClientUmbrellaIdINTEGER null,
-- Flag for errors
ErrorCode INTEGER null,
-- Flag for Insert/Update
INSERTIND INTEGER NULL
)
-- Insert all DISTINCT Client Codes with Insert Indicator ON (1)
INSERT INTO #TMP_CLIENTS_PROCESSING (CLIENTCODE, ErrorCode, INSERTIND)
SELECT Distinct CLIENTCODE, 0, 1
FROM vsImport..ImportClients
--Create Index on ClientCode
create index #tmp_clients_process_clientcode on #TMP_CLIENTS_PROCESSING(ClientCode)
-- Update all records with remaining values from ImportClients
UPDATE #TMP_CLIENTS_PROCESSING
SET ApplicationId = ISNULL(IC.ApplicationId, -1),
RecordId = ISNULL(IC.RecordId, -1),
LastName = ISNULL(IC.LastName, ''),
FirstName = ISNULL(IC.Firstname, ''),
RRCode= ISNULL(IC.RRCode,''),
InvestorTypeId=ISNULL(IC.InvestorTypeId, -1),
PostalCode=ISNULL(IC.PostalCode,''),
ResidenceShortName=ISNULL(IC.ResidenceShortName,''),
CountryCode=ISNULL(IC.CountryCode,''),
JointCLientAccountId=ISNULL(IC.JointClientAccountId,-1),
SIN=ISNULL(IC.SIN,''),
SpouseName=ISNULL(IC.SpouseName,''),
SpouseSin=ISNULL(IC.SpouseSIN,''),
City=ISNULL(IC.City,''),
LanguageId=ISNULL(IC.LanguageId,-1),
Address1=ISNULL(IC.Address1,''),
Address2=ISNULL(IC.Address2,''),
Address3=ISNULL(IC.Address3,''),
Address4=ISNULL(IC.Address4,''),
Address5=ISNULL(IC.Address5,''),
Address6=ISNULL(IC.Address6,''),
Address7=ISNULL(IC.Address7,''),
Address8=ISNULL(IC.Address8,''),
Phone1=ISNULL(IC.Phone1,''),
Phone2=ISNULL(IC.Phone2,''),
Phone3 =ISNULL(IC.Phone3,''),
Phone4 =ISNULL(IC.Phone4,''),
Email1 =ISNULL(IC.Email1,''),
Email2 =ISNULL(IC.Email2,''),
Email3 =ISNULL(IC.Email3,''),
Email4 =ISNULL(IC.Email4,''),
Status =ISNULL(IC.Status,-1) ,
ClientUmbrellaId = 0
FROM vsImport..ImportClients as IC
WHERE #TMP_CLIENTS_PROCESSING.ClientCode = IC.ClientCode
--select * from vsImport..ImportClients
--Nov 23 2006
--Only for AppId = 2
--Update Blank or Null LastNames with FirstName
--Dec 1 2006
--Jan 25 2007 - fix temp table reference
IF @AppId=2
BEGIN
UPDATE #TMP_CLIENTS_PROCESSING
SET LastName = FirstName,
FirstName = ''
WHERE (LastName IS NULL OR len(LastName)=0) AND
ApplicationId=@AppId
END
-- Update with CountryID
UPDATE #TMP_CLIENTS_PROCESSING
SET #TMP_CLIENTS_PROCESSING.CountryId= C.CountryId
FROM vsNIAD..Country as C
WHERE#TMP_CLIENTS_PROCESSING.CountryCode = C.CountryCode and C.Appid = @Appid
SELECT @FirmID = null
-- Retrieve the FirmId
SELECT @FirmID = FirmId FROM vsNIAD..FirmsTable (@AppId, @FirmID, null )
-- Update with FirmID
UPDATE #TMP_CLIENTS_PROCESSING
SET #TMP_CLIENTS_PROCESSING.FirmId = @FirmID
-- Update with RRCodeID
UPDATE #TMP_CLIENTS_PROCESSING
SET #TMP_CLIENTS_PROCESSING.RRcodeId= RRC.RRCodeId
FROM vsNIAD..RRcodes as RRC
WHERERRC.RRCOde = #TMP_CLIENTS_PROCESSING.RRCode and
RRC.FirmId = #TMP_CLIENTS_PROCESSING.FirmId and
RRC.FirmId=@FirmId
-- Update with ResidenceID
UPDATE #TMP_CLIENTS_PROCESSING
SET #TMP_CLIENTS_PROCESSING.ResidenceId= RES.ResidenceId
FROM vsNIAD..Residence as RES
WHERERES.ShortName = #TMP_CLIENTS_PROCESSING.ResidenceShortName and RES.Appid=@AppId
-- Set Error Flag to 1 for records with missing values
UPDATE #TMP_CLIENTS_PROCESSING
SetErrorCode = 1
WHERE
LEN(FirmId) =0 or
LEN(Isnull(CountryId,0)) = 0 or
LEN(Isnull(RRCodeId,0)) = 0 or RRCodeId is null or
LEN(Isnull(RRCode,'')) =0 or
LEN(Isnull(ResidenceID,0)) = 0 or
LEN(Isnull(Lastname,'')) = 0 or
InvestorTYpeid =-1 or InvestorTypeId is null or
LEN(Isnull(CLientCode,'')) = 0 or
LanguageId = -1 or LanguageId not in (1,2) or LanguageId is null or
Status = -1 or Status not in (1,2) or
LEN(Isnull(ResidenceShortName,'')) = 0 or
ApplicationId = -1 or
ApplicationId <> @AppId
-- Update with ClientID and INSERT Indicator OFF
-- Records (ClientCodes) that already exist and have a ClientID will need to be updated
-- and not inserted.
UPDATE #TMP_CLIENTS_PROCESSING
SET #TMP_CLIENTS_PROCESSING.CLIENTID = CLIENTS.CLIENTID,
#TMP_CLIENTS_PROCESSING.INSERTIND = 0
FROM vsNiad..CLIENTS CLIENTS
--INNER JOIN vsNiad..RRCODES RRCODES ON (CLIENTS.RRCODEID = RRCODES.RRCODEID)
WHERE #TMP_CLIENTS_PROCESSING.CLIENTCODE = CLIENTS.CLIENTCODE
--#TMP_CLIENTS_PROCESSING.RRCODEID = RRCODES.RRCODEID AND
-- #TMP_CLIENTS_PROCESSING.FIRMID = RRCODES.FIRMID
--INSERT INVALID RECORDS INTO LOG-----------------------------------------------------------------------------------------------------
INSERT INTO vsImport..import_log SELECT 'Clients...Invalid Fields','ImportClients', ClientCode, 0, RecordId, 1,getdate()
FROM #TMP_CLIENTS_PROCESSING
WHERE ERRORCODE = 1
SET @RecordsCount = @RecordsCount + (SELECT COUNT (*) FROM #TMP_CLIENTS_PROCESSING WHERE ERRORCODE = 1)
/* 1. -------------------------------------------------------------------------------------------------------------------- */
/* ==================================================================================================================== */
/* -- BATCH INSERT RECORDS--------------------------------------------------------------------------------------------- */
/* All records (ClientCodes) that have a clientID and do not have an INSERT INDICATOR will be updated*/
/* All records that do not have a ClientID and do not have an INSERT INDICATOR will be inserted*/
/* ==================================================================================================================== */
-- BATCH INSERTS
SELECT @RowByRow = 0, @BatchInsertStatus = 0
BEGIN TRANSACTION
/* ---------------------------------------------------------------------------------------------------------- */
/* -- INSERT RECORDS INTO CLIENTS TABLE---------------------------------------------------------------------- */
INSERT INTO vsImport..import_log SELECT 'Clients...Start-Batch Inserts','ImportClients',0,0,0,0 ,getdate()
INSERT INTO vsNiad..CLIENTS(ClientCode,LastName,FirstName,ClientStatusTypeId,RRCodeId,NiadInvestorTypeId,PostalCode, Residence_ID, Country_ID, SIN, SpouseName, SpouseSIN, City, LanguageID, ClientUmbrellaId, ClientName)
SELECT ClientCode ,LastName,FirstName,Status,RRCodeId,InvestorTypeId, PostalCode, ResidenceID, CountryID, SIN, SpouseName, SpouseSIN, City, LanguageID, ClientUmbrellaId, LastName+' '+IsNull(FirstName,'')
FROM #TMP_CLIENTS_PROCESSING
WHERE INSERTIND = 1 and ErrorCode = 0
SET @intErrorCode=@@error
-- Check errors
IF @intErrorCode <> 0
BEGIN
--rollback and process row-by-row
ROLLBACK TRANSACTION
INSERT INTO vsImport..import_log select 'Clients-Batch Insert...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
SELECT @RowByRow = 1
SELECT @BatchInsertStatus =1
END
ELSE
BEGIN
-- update RECENT inserted records with ClientID
UPDATE #TMP_CLIENTS_PROCESSING
SET #TMP_CLIENTS_PROCESSING.CLIENTID = CLIENTS.CLIENTID
FROM vsNiad..CLIENTS CLIENTS
--INNER JOIN vsNiad..RRCODES RRCODES ON (CLIENTS.RRCODEID = RRCODES.RRCODEID)
WHERE #TMP_CLIENTS_PROCESSING.CLIENTCODE = CLIENTS.CLIENTCODE AND
--#TMP_CLIENTS_PROCESSING.RRCODEID = RRCODES.RRCODEID AND
-- #TMP_CLIENTS_PROCESSING.FIRMID = RRCODES.FIRMID AND
#TMP_CLIENTS_PROCESSING.INSERTIND = 1 And
#TMP_CLIENTS_PROCESSING.ErrorCode = 0
/* ------------------------------------------------------------------------------------------------------------------ */
/* -- INSERT ADDRESS, EMAIL, AND PHONE------------------------------------------------------------------------------- */
-- Reset Variables in case...
INSERT INTO vsImport..import_log SELECT 'Clients-Address...Start-Inserts','ImportClients',0,0,0,0 ,getdate()
-- INSERT ADDRESSES
-- Loop through the number of Addresses
-- While @a < @numAddresses
WHILE @a<=8
BEGIN
SELECT @Address =
(
SELECT CASE @a
when 1 then 'Address1'
when 2 then 'Address2'
when 3 then 'Address3'
when 4 then 'Address4'
when 5 then 'Address5'
when 6 then 'Address6'
when 7 then 'Address7'
when 8 then 'Address8'
END
)
SELECT @sql = 'INSERT INTO vsNiad..ClientAddress (RowId, ClientId, Address) '
SELECT @sql = @sql +' SELECT ' + cast(@a as varchar(1)) + ',ClientId,'+ @Address
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING WHERE INSERTIND = 1 and ErrorCode = 0 and '
SELECT @sql = @sql +'len('+ @Address+')>0'
EXEC (@sql)
SELECT @sql =''
SET @intErrorCode=@@error
if @intErrorCode <> 0
begin
INSERT INTO vsImport..import_log SELECT 'Clients-Address...InvalidFields','ImportClients',@sql,0,0,0 ,getdate()
end
SET @a=@a+1
END
-- End of Address INSERTS
-- INSERT EMAILS
-- Loop through the number of Emails
-- While @a < @numEmails
INSERT INTO vsImport..import_log SELECT 'Clients-Emails...Start-Inserts','ImportClients',0,0,0,0 ,getdate()
WHILE @a<=4
BEGIN
SELECT @Email =
(
SELECT CASE @a
when 1 then 'Email1'
when 2 then 'Email2'
when 3 then 'Email3'
when 4 then 'Email4'
END
)
SELECT @sql = 'INSERT INTO vsNiad..ClientEmail (RowId, ClientId, Email) '
SELECT @sql = @sql +' SELECT ' + cast(@a as varchar(1)) + ',ClientId,'+ @Email
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING WHERE INSERTIND = 1 and ErrorCode = 0 and '
SELECT @sql = @sql +'len('+ @Email+')>0'
EXEC (@sql)
SELECT @sql =''
SET @intErrorCode=@@error
if @intErrorCode <> 0
begin
INSERT INTO vsImport..import_log SELECT 'Clients-Emails...InvalidFields','ImportClients',@sql,0,0,0 ,getdate()
end
SET @a=@a+1
END
-- End of Emails INSERTS
-- INSERT PHONE
-- Loop through the number of Phone Numbers
-- While @a < @numPhones
INSERT INTO vsImport..import_log SELECT 'Clients-Phone...Start-Inserts','ImportClients',0,0,0,0 ,getdate()
WHILE @a<=4
BEGIN
SELECT @Phone =
(
SELECT CASE @a
when 1 then 'Phone1'
when 2 then 'Phone2'
when 3 then 'Phone3'
when 4 then 'Phone4'
END
)
SELECT @sql = 'INSERT INTO vsNiad..ClientPhone (RowId, ClientId, PhoneNumber) '
SELECT @sql = @sql +' SELECT ' + cast(@a as varchar(1)) + ',ClientId,'+ @Phone
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING WHERE INSERTIND = 1 and ErrorCode = 0 and '
SELECT @sql = @sql +'len('+ @Phone+')>0'
EXEC (@sql)
SELECT @sql =''
SET @intErrorCode=@@error
if @intErrorCode <> 0
begin
INSERT INTO vsImport..import_log SELECT 'Clients-Phones...InvalidFields','ImportClients',@sql,0,0,0 ,getdate()
end
SET @a=@a+1
END
-- End of Phone INSERTS
IF @intErrorCode = 0
BEGIN
COMMIT TRANSACTION
DECLARE @BatchInsert INTEGER
SET @BatchInsert = (SELECT COUNT(*)
FROM #TMP_CLIENTS_PROCESSING
WHERE INSERTIND = 1 and ErrorCode = 0)
SET @RecordsCount = @RecordsCount + @BatchInsert
INSERT INTO vsImport..import_log SELECT 'Clients...End-Batch Inserts','ImportClients',0,0,@BatchInsert,0 ,getdate()
END
ELSE BEGIN
ROLLBACK TRANSACTION
INSERT INTO vsImport..import_log select 'Clients-Insert...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
SELECT @RowByRow = 1
SELECT @BatchInsertStatus = 1
END
END
/* ------------------------------------------------------------------------------------------------------------------ */
-- BATCH UPDATES
IF (@RowByRow = 0 and @BatchInsertStatus = 0)
BEGIN
INSERT INTO vsImport..import_log SELECT 'Clients...Start-Batch Updates','ImportClients',0,0,0,0 ,getdate()
BEGIN TRANSACTION
UPDATE CLI
SET CLI.Lastname = TCP.LastName,
CLI.FirstName = TCP.FirstName,
CLI.ClientStatusTypeId = TCP.Status,
CLI.RRCodeId = TCP.RRCodeId,
CLI.NiadInvestorTypeId = TCP.InvestorTypeId,
CLI.PostalCode = TCP.PostalCode,
CLI.Residence_ID = TCP.ResidenceID,
CLI.Country_ID = TCP.CountryID,
CLI.SIN = TCP.SIN,
CLI.SpouseName = TCP.spouseName,
CLI.SpouseSIN = TCP.SpouseSIN,
CLI.City = TCP.City,
CLI.LanguageID = TCP.LanguageID,
CLI.ClientName = TCP.LastName+' '+IsNull(TCP.FirstName,'')
FROM #TMP_CLIENTS_PROCESSING TCP,
VSNIAD..Clients CLI
WHERE
CLI.ClientId = TCP.ClientId And
TCP.INSERTIND = 0 and
ErrorCode = 0
SET @intErrorCode=@@error
-- Check errors
IF @intErrorCode <> 0
BEGIN
--rollback and process row-by-row
ROLLBACK TRANSACTION
INSERT INTO vsImport..import_log select 'Clients-Batch Update...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
SELECT @RowByRow = 1
END
ELSE
BEGIN
-- UPDATE ADDRESSES
-- Loop through the number of Addresses
-- While @a < @numAddresses
WHILE @a<=8
BEGIN
SELECT @Address =
(
SELECT CASE @a
when 1 then 'Address1'
when 2 then 'Address2'
when 3 then 'Address3'
when 4 then 'Address4'
when 5 then 'Address5'
when 6 then 'Address6'
when 7 then 'Address7'
when 8 then 'Address8'
END
)
SELECT @sql = 'UPDATE CAD SET Address = TCP.'
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING TCP, vsNiad..CLIENTADDRESS CAD '
SELECT @sql = @sql +'WHERE TCP.ClientId = CAD.ClientId And TCP.INSERTIND = 0 and TCP.ErrorCode = 0'
SELECT @sql = @sql +' AND CAD.ROWID = ' + cast(@a as varchar)
EXEC (@sql)
SELECT @sql =''
--Insert New Addresses that did not exist and came as updates
SELECT @sql = 'INSERT INTO vsNiad..ClientAddress (RowId, ClientId, Address) '
SELECT @sql = @sql +' SELECT ' + cast(@a as varchar(1)) + ',ClientId,'+ @Address
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING WHERE INSERTIND = 0 and ErrorCode = 0 and '
SELECT @sql = @sql +'len('+ @Address+')>0 And ClientId NOT IN ('
SELECT @sql = @sql +' SELECT TCP.ClientId FROM #TMP_CLIENTS_PROCESSING TCP '
SELECT @sql = @sql +' INNER JOIN vsNiad..ClientAddress CAD on (TCP.ClientId = CAD.ClientId) '
SELECT @sql = @sql +' WHERE TCP.INSERTIND = 0 and TCP.ErrorCode = 0 And'
SELECT @sql = @sql +' CAD.ROWID = ' + cast(@a as varchar) + ' )'
EXEC (@sql)
SELECT @sql =''
SET @intErrorCode=@@error
SET @a=@a+1
END
-- End of Address UPDATES
-- UPDATE EMAILS
-- Loop through the number of Emails
-- While @a < @numEmails
WHILE @a<=4
BEGIN
SELECT @Email =
(
SELECT CASE @a
when 1 then 'Email1'
when 2 then 'Email2'
when 3 then 'Email3'
when 4 then 'Email4'
END
)
SELECT @sql = 'UPDATE CEM SET Email = TCP.'
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING TCP, vsNiad..ClientEmail CEM '
SELECT @sql = @sql +'WHERE TCP.ClientId = CEM.ClientId And TCP.INSERTIND = 0 and ErrorCode = 0'
SELECT @sql = @sql +' AND CEM.ROWID = ' + cast(@a as varchar)
EXEC (@sql)
SELECT @sql =''
--Insert New Emails that did not exist and came as updates
SELECT @sql = 'INSERT INTO vsNiad..ClientEmail (RowId, ClientId, Email) '
SELECT @sql = @sql +' SELECT ' + cast(@a as varchar(1)) + ',ClientId,'+ @Email
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING WHERE INSERTIND = 0 and ErrorCode = 0 and '
SELECT @sql = @sql +' len('+ @Email+')>0 And ClientId NOT IN ('
SELECT @sql = @sql +' SELECT TCP.ClientId FROM #TMP_CLIENTS_PROCESSING TCP '
SELECT @sql = @sql +' INNER JOIN vsNiad..ClientEmail CEM ON (TCP.ClientId = CEM.ClientId) '
SELECT @sql = @sql +' WHERE TCP.INSERTIND = 0 and TCP.ErrorCode = 0 And'
SELECT @sql = @sql +' CEM.ROWID = ' + cast(@a as varchar) + ' )'
EXEC (@sql)
SELECT @sql =''
SET @intErrorCode=@@error
SET @a=@a+1
END
-- End of Emails Updates
-- UPDATE PHONE
-- Loop through the number of Phone Numbers
-- While @a < @numPhones
WHILE @a<=4
BEGIN
SELECT @Phone =
(
SELECT CASE @a
when 1 then 'Phone1'
when 2 then 'Phone2'
when 3 then 'Phone3'
when 4 then 'Phone4'
END
)
SELECT @sql = 'UPDATE CPH SET PhoneNumber = TCP.'
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING TCP, vsNiad..ClientPhone CPH '
SELECT @sql = @sql +'WHERE TCP.ClientId = CPH.ClientId And TCP.INSERTIND = 0 and ErrorCode = 0'
SELECT @sql = @sql +' AND CPH.ROWID = ' + cast(@a as varchar)
EXEC (@sql)
SELECT @sql =''
SELECT @sql = 'INSERT INTO vsNiad..ClientPhone (RowId, ClientId, PhoneNumber) '
SELECT @sql = @sql +' SELECT ' + cast(@a as varchar(1)) + ',ClientId,'+ @Phone
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING WHERE INSERTIND = 0 and ErrorCode = 0 and '
SELECT @sql = @sql +' len('+ @Phone+')>0 And ClientId NOT IN ('
SELECT @sql = @sql +' SELECT TCP.ClientId FROM #TMP_CLIENTS_PROCESSING TCP '
SELECT @sql = @sql +' INNER JOIN vsNiad..ClientPhone CPH ON (TCP.ClientId = CPH.ClientId) '
SELECT @sql = @sql +' WHERE TCP.INSERTIND = 0 and TCP.ErrorCode = 0 And'
SELECT @sql = @sql +' CPH.ROWID = ' + cast(@a as varchar) + ' )'
EXEC (@sql)
SELECT @sql =''
SET @intErrorCode=@@error
SET @a=@a+1
END
IF @intErrorCode = 0
BEGIN
COMMIT TRANSACTION
DECLARE @BatchUpdate INTEGER
SET @BatchUpdate = (SELECT COUNT(*)
FROM #TMP_CLIENTS_PROCESSING TCP, VSNIAD..Clients CLI
WHERE CLI.ClientId = TCP.ClientId And TCP.INSERTIND = 0 and ErrorCode = 0)
SET @RecordsCount = @RecordsCount + @BatchUpdate
INSERT INTO vsImport..import_log SELECT 'Clients...End-Batch Updates','ImportClients',0,0,@BatchUpdate,0 ,getdate()
END
ELSE BEGIN
ROLLBACK TRANSACTION
INSERT INTO vsImport..import_log select 'Clients-Updates...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
SELECT @RowByRow = 1
SELECT @BatchUpdateStatus = 1
END
END
END
/* 3. -------------------------------------------------------------------------------------------------------------------- */
/* ==================================================================================================================== */
/* --ROW BY ROW - UPDATE/INSERT RECORDS--------------------------------------------------------------------------------------------- */
/* All records (ClientCodes) that have a clientID and do not have an INSERT INDICATOR will be updated*/
/* All records that do not have a ClientID and do not have an INSERT INDICATOR will be inserted*/
/* ==================================================================================================================== */
--- CAN'T LOOK UP CLIENTID BASED ON CLIENT CODE ONLY.!!!!!!!!
IF @RowByRow = 1
BEGIN
INSERT INTO vsImport..import_log SELECT 'Clients...Start-Row by Row Processing','ImportClients',0,0,0,0 ,getdate()
Select @iRowCount = 1
Select @iCurrentRowId = 0
Select @iNextRowId = 0
-- Loop through the records marked for UPDATE and INSERT ONLY (FLAG=0 & 1)
WHILE (@iRowCount <= (Select count(*) from #TMP_CLIENTS_PROCESSING where ERRORCODE = 0))
BEGIN
SELECT @iNextRowId = (Select min(RecordId) from #TMP_CLIENTS_PROCESSING WHERE ErrorCode = 0 and RecordId > @iCurrentRowId)
SELECT @iInsertIND = -1
SELECT @iInsertIND = (SELECT INSERTIND FROM #TMP_CLIENTS_PROCESSING WHERE RecordId = @iNextRowid)
/* ------------------------------------------------------------------------------------------------------------------ */
/* -- UPDATE CLIENTS------------------------------------------------------------------------------------------------- */
IF @iInsertIND = 0
BEGIN
INSERT INTO vsImport..import_log SELECT 'Clients...Start-Row by Row - Updates','ImportClients',0,0,0,0 ,getdate()
BEGIN TRANSACTION
/* ---------------------------------------------------------------------------------------------------------- */
/* -- UPDATE MAIN DETAILS OF CLIENT-------------------------------------------------------------------------- */
UPDATE CLI
SET CLI.Lastname = TCP.LastName,
CLI.FirstName = TCP.FirstName,
CLI.ClientStatusTypeId = TCP.Status,
CLI.RRCodeId = TCP.RRCodeId,
CLI.NiadInvestorTypeId = TCP.InvestorTypeId,
CLI.PostalCode = TCP.PostalCode,
CLI.Residence_ID = TCP.ResidenceID,
CLI.Country_ID = TCP.CountryID,
CLI.SIN = TCP.SIN,
CLI.SpouseName = TCP.spouseName,
CLI.SpouseSIN = TCP.SpouseSIN,
CLI.City = TCP.City,
CLI.LanguageID = TCP.LanguageId,
CLI.ClientName = TCP.LastName+' '+IsNull(TCP.FirstName,'')
FROM #TMP_CLIENTS_PROCESSING TCP,
VSNIAD..Clients CLI
WHERE
TCP.RecordId = @iNextRowId and
CLI.ClientId = TCP.ClientId and
ErrorCode = 0
/* ------------------------------------------------------------------------------------------------------------------ */
/* -- UPDATE ADDRESS, EMAIL, AND PHONE------------------------------------------------------------------------------- */
Select @ClientId=''
Select @ClientCode=''
Select @Address=''
Select @Address1=''
Select @Address2=''
Select @Address3=''
Select @Address4=''
Select @Address5=''
Select @Address6=''
Select @Address7=''
Select @Address8=''
Select @Email=''
Select @Email1=''
Select @Email2=''
Select @Email3=''
Select @Email4=''
-- PHONE
Select @Phone=''
Select @Phone1=''
Select @Phone2=''
Select @Phone3=''
Select @Phone4=''
-- Retrieve Address, Email, Phone into variables
Select @ClientId = ClientId,
@ClientCode = ClientCode,
@Address1= Address1,
@Address2 = Address2,
@Address3 = Address3,
@Address4 = Address4,
@Address5 = Address5,
@Address6 = Address6,
@Address7 = Address7,
@Address8 = Address8,
@Email1 = Email1,
@Email2 = Email2,
@Email3 = Email3,
@Email4 = Email4,
@Phone1 = Phone1,
@Phone2 = Phone2,
@Phone3 = Phone3,
@Phone4 = Phone4
From #TMP_CLIENTS_PROCESSING
Where RecordId = @iNextRowId and ErrorCode = 0
-- UPDATE ADDRESSES
-- Loop through the number of Addresses
-- While @a < @numAddresses
INSERT INTO vsImport..import_log SELECT 'Clients-Address...Row by Row - Updates','ImportClients',0,0,0,0 ,getdate()
Select @a=1
WHILE @a<=8
BEGIN
SELECT @Address =
(
SELECT CASE @a
when 1 then @Address1
when 2 then @Address2
when 3 then @Address3
when 4 then @Address4
when 5 then @Address5
when 6 then @Address6
when 7 then @Address7
when 8 then @Address8
END
)
IF len(@Address)!=0
BEGIN
EXECUTE @intErrorCode = vsNiad..ClientAddress_I_U @ClientId, @a, @Address, 4
--Select @intErrorCode = @@error
--if (@intErrorCode<>0) goto error_label
END
Set @a=@a+1
END
-- End of Address Updates
-- UPDATE EMAILS
-- Loop through the number of Emails
-- While @a < @numEmails
Select @a=1
WHILE @a<=4
BEGIN
SELECT @Email =
(
SELECT CASE @a
when 1 then @Email1
when 2 then @Email2
when 3 then @Email3
when 4 then @Email4
END
)
IF len(@Email)!=0
BEGIN
EXECUTE @intErrorCode = vsNiad..ClientEmail_I_U @ClientId, @a, @Email, 4
--Select @intErrorCode = @@error
--if (@intErrorCode<>0) goto error_label
END
Set @a=@a+1
END
-- End of Emails Updates
-- UPDATE PHONE
-- Loop through the number of Phone Numbers
-- While @a < @numPhones
SELECT @a=1
WHILE @a<=4
BEGIN
SELECT @Phone =
(
SELECT CASE @a
when 1 then @Phone1
when 2 then @Phone2
when 3 then @Phone3
when 4 then @Phone4
END
)
IF len(@Phone)!=0
BEGIN
EXECUTE @intErrorCode = vsNiad..ClientPhone_I_U @ClientId, @a, @Phone, 4
SET @intErrorCode=@@error
IF @intErrorCode <> 0
BEGIN
INSERT INTO vsImport..import_log select 'Clients-Main Insert...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
END
END
Set @a=@a+1
END
-- CHECK FOR ERRORS AND COMMIT TRANSACTION
IF @intErrorCode = 0
BEGIN
COMMIT TRANSACTION
SET @RecordsCount = @RecordsCount + 1
END
ELSE BEGIN
-- ON ERROR -> ROLLBACK AND WRITE FAILED RECORD TO LOG
ROLLBACK TRANSACTION
SET @RecordsCount = @RecordsCount + 1
INSERT INTO vsImport..import_log select 'Clients-Insert...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
END
--END OF UPDATE PROCESS
END
/* ------------------------------------------------------------------------------------------------------------------ */
/* ------------------------------------------------------------------------------------------------------------------ */
/* -- INSERT CLIENTS------------------------------------------------------------------------------------------------- */
/* -- Only insert if the Batch Insert failed. The Batch Insert may be successful and only update may fail. ---------- */
IF (@iInsertIND = 1 and @BatchInsertStatus = 1)
BEGIN
BEGIN TRANSACTION
/* ---------------------------------------------------------------------------------------------------------- */
/* -- INSERT RECORDS INTO CLIENTS TABLE---------------------------------------------------------------------- */
--SET IDENTITY_INSERT VSNIAD..CLIENTS ON
INSERT INTO vsNiad..CLIENTS(ClientCode,LastName,FirstName,ClientStatusTypeId,RRCodeId,NiadInvestorTypeId,PostalCode, Residence_ID, Country_ID, SIN, SpouseName, SpouseSIN, City, LanguageID, ClientUmbrellaId, ClientName)
SELECT ClientCode ,LastName,FirstName,Status,RRCodeId,InvestorTypeId, PostalCode, ResidenceID, CountryID, SIN, SpouseName, SpouseSIN, City, LanguageID, ClientUmbrellaId, LastName+' '+IsNull(FirstName,'')
FROM #TMP_CLIENTS_PROCESSING
WHERE RecordId = @iNextRowId and ErrorCode = 0
--SET IDENTITY_INSERT VSNIAD..CLIENTS OFF
Select @ClientId = Scope_Identity()
SET @intErrorCode=@@error
IF @intErrorCode <> 0
BEGIN
INSERT INTO vsImport..import_log select 'Clients-Main Insert...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
END
/* ------------------------------------------------------------------------------------------------------------------ */
/* -- INSERT ADDRESS, EMAIL, AND PHONE------------------------------------------------------------------------------- */
-- Reset Variables in case...
Select @Address=''
Select @Address1=''
Select @Address2=''
Select @Address3=''
Select @Address4=''
Select @Address5=''
Select @Address6=''
Select @Address7=''
Select @Address8=''
Select @Email=''
Select @Email1=''
Select @Email2=''
Select @Email3=''
Select @Email4=''
-- PHONE
Select @Phone=''
Select @Phone1=''
Select @Phone2=''
Select @Phone3=''
Select @Phone4=''
-- Retrieve Address, Email, Phone into variables
Select @Address1= Address1,
@Address2 = Address2,
@Address3 = Address3,
@Address4 = Address4,
@Address5 = Address5,
@Address6 = Address6,
@Address7 = Address7,
@Address8 = Address8,
@Email1 = Email1,
@Email2 = Email2,
@Email3 = Email3,
@Email4 = Email4,
@Phone1 = Phone1,
@Phone2 = Phone2,
@Phone3 = Phone3,
@Phone4 = Phone4
From #TMP_CLIENTS_PROCESSING
Where RecordId = @iNextRowId
INSERT INTO vsImport..import_log SELECT 'Clients-Address...Row by Row - Inserts','ImportClients',0,0,0,0 ,getdate()
-- INSERT ADDRESSES
-- Loop through the number of Addresses
-- While @a < @numAddresses
Select @a=1
WHILE @a<=8
BEGIN
SELECT @Address =
(
SELECT CASE @a
when 1 then @Address1
when 2 then @Address2
when 3 then @Address3
when 4 then @Address4
when 5 then @Address5
when 6 then @Address6
when 7 then @Address7
when 8 then @Address8
END
)
IF len(@Address)!=0
BEGIN
EXECUTE @intErrorCode = vsNiad..ClientAddress_I_U @ClientId, @a, @Address, 2
--SET @intErrorCode=@@error
IF @intErrorCode <> 0
BEGIN
INSERT INTO vsImport..import_log select 'Clients-Address...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
END
END
Set @a=@a+1
END
-- End of Address INSERTS
-- INSERT EMAILS
-- Loop through the number of Emails
-- While @a < @numEmails
Select @a=1
WHILE @a<=4
BEGIN
SELECT @Email =
(
SELECT CASE @a
when 1 then @Email1
when 2 then @Email2
when 3 then @Email3
when 4 then @Email4
END
)
IF len(@Email)!=0
BEGIN
EXECUTE @intErrorCode = vsNiad..ClientEmail_I_U @ClientId, @a, @Email, 2
--SET @intErrorCode=@@error
IF @intErrorCode <> 0
BEGIN
INSERT INTO vsImport..import_log select 'Clients-Email...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
END
END
Set @a=@a+1
END
-- End of Emails INSERTS
-- INSERT PHONE
-- Loop through the number of Phone Numbers
-- While @a < @numPhones
Select @a=1
WHILE @a<=4
BEGIN
SELECT @Phone =
(
SELECT CASE @a
when 1 then @Phone1
when 2 then @Phone2
when 3 then @Phone3
when 4 then @Phone4
END
)
IF len(@Phone)!=0
BEGIN
EXECUTE @intErrorCode = vsNiad..ClientPhone_I_U @ClientId, @a, @Phone, 2
SET @intErrorCode=@@error
IF @intErrorCode <> 0
BEGIN
INSERT INTO vsImport..import_log select 'Clients-Phone...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
END
END
Set @a=@a+1
END
-- End of Phone INSERTS
IF @intErrorCode = 0
BEGIN
COMMIT TRANSACTION
SET @RecordsCount = @RecordsCount + 1
END
ELSE BEGIN
ROLLBACK TRANSACTION
SET @RecordsCount = @RecordsCount + 1
INSERT INTO vsImport..import_log select 'Clients-Insert...Failed','ImportClients',@ClientCode,@intErrorCode,@ICurrentRowId,1,getdate()
SELECT @intErrorCode = @@Error
END
--END OF INSERT PROCESS
END
/* ------------------------------------------------------------------------------------------------------------------ */
--Increment the RowCount
SET @iRowCount=@iRowCount+1
SET @iCurrentRowId = @iNextRowId
--END OF LOOP
END
INSERT INTO vsImport..import_log SELECT 'Clients...End-Row by Row - Updates','ImportClients',0,0,@RecordsCount,0 ,getdate()
--End of Row By Row
END
--Concatenate addresses into new column "Address" in Clients table
EXEC vsNiad..GenerateClientAddress @Appid = @Appid
INSERT INTO vsImport..import_log SELECT 'Clients...End','ImportClients',0,@intErrorCode,ISNULL(@RecordsCount, 0),0,getdate()
GO
July 14, 2008 at 9:51 pm
Riga,
I'm still looking at the code but I did find a problem that's prevalent throughout the code where there are updates...
See anything wrong with the following code?
[font="Courier New"]-- Update all records with remaining values from ImportClients
UPDATE #TMP_CLIENTS_PROCESSING
SET ApplicationId = ISNULL(IC.ApplicationId, -1),
RecordId = ISNULL(IC.RecordId, -1),
LastName = ISNULL(IC.LastName, ''),
FirstName = ISNULL(IC.Firstname, ''),
RRCode= ISNULL(IC.RRCode,''),
InvestorTypeId=ISNULL(IC.InvestorTypeId, -1),
PostalCode=ISNULL(IC.PostalCode,''),
ResidenceShortName=ISNULL(IC.ResidenceShortName,''),
CountryCode=ISNULL(IC.CountryCode,''),
JointCLientAccountId=ISNULL(IC.JointClientAccountId,-1),
SIN=ISNULL(IC.SIN,''),
SpouseName=ISNULL(IC.SpouseName,''),
SpouseSin=ISNULL(IC.SpouseSIN,''),
City=ISNULL(IC.City,''),
LanguageId=ISNULL(IC.LanguageId,-1),
Address1=ISNULL(IC.Address1,''),
Address2=ISNULL(IC.Address2,''),
Address3=ISNULL(IC.Address3,''),
Address4=ISNULL(IC.Address4,''),
Address5=ISNULL(IC.Address5,''),
Address6=ISNULL(IC.Address6,''),
Address7=ISNULL(IC.Address7,''),
Address8=ISNULL(IC.Address8,''),
Phone1=ISNULL(IC.Phone1,''),
Phone2=ISNULL(IC.Phone2,''),
Phone3 =ISNULL(IC.Phone3,''),
Phone4 =ISNULL(IC.Phone4,''),
Email1 =ISNULL(IC.Email1,''),
Email2 =ISNULL(IC.Email2,''),
Email3 =ISNULL(IC.Email3,''),
Email4 =ISNULL(IC.Email4,''),
Status =ISNULL(IC.Status,-1) ,
ClientUmbrellaId = 0
FROM vsImport..ImportClients as IC
WHERE #TMP_CLIENTS_PROCESSING.ClientCode = IC.ClientCode
[/font]
Most people never see it... the first time I ran into code like that, it took me about 4 hours to figure it out. We had a process that slammed 4 CPU's into the wall for 2 solid hours... when we fixed the problem, the code took less than 2 minutes to run.
I don't know what causes it to run haywire sometimes, but you've actually used an undocumented form of Update that sometimes takes the wrong path. I've lost track of the Microsoft document that talks about it because I thought I'd never run into it again. Basically the document says that if this form of update is used, and you have a multi-cpu box where parallelism is spawed, and you have just the right kind of indexes, the problem will occur.
Here's the simplified version of the problem... You're updates are using the undocument UPDATE that takes the following basic form...
[font="Courier New"] UPDATE TableA
SET SomeTableAColumn = b.SomeTableBColumn
FROM TableB b
WHERE TableA.SomeOtherColumn = TableB.SomeOtherColumn[/font]
Notice the red stuff above... you've made reference to TableA in the WHERE clause, but it's not in the FROM clause. THAT's the illegal form of UPDATE and THAT's the killer. It doesn't always cause a train wreck, but when it does, it's a real killer.
The correct form for such "joined Updates" is...
[font="Courier New"] UPDATE TableA
SET SomeTableAColumn = b.SomeTableBColumn
FROM TableB b
INNER JOIN TableA a
ON a.SomeOtherColumn = b.SomeOtherColumn[/font]
Notice that in "Joined Updates", the object of the update MUST be in the FROM clause. You can cheat a bit by using an alias ("a" in this case) on the UPDATE line instead of the full tablename because that works... BUT, it too is an undocumented form of UPDATE and could go haywire on some future service pack or fix.
Lemme just say it again... the object of the UPDATE must be in the FROM clause on "Joined Updates" or this problem may occur.
{EDIT} You have several updates in this condition and they have to be repaired before you do anything else.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2008 at 10:00 pm
Yep... I found that undocumented alias form of update, too. While it seems to work quite well, you may not want to take a chance on it...
[font="Courier New"] UPDATE CLI
SET CLI.Lastname = TCP.LastName,
CLI.FirstName = TCP.FirstName,
CLI.ClientStatusTypeId = TCP.Status,
CLI.RRCodeId = TCP.RRCodeId,
CLI.NiadInvestorTypeId = TCP.InvestorTypeId,
CLI.PostalCode = TCP.PostalCode,
CLI.Residence_ID = TCP.ResidenceID,
CLI.Country_ID = TCP.CountryID,
CLI.SIN = TCP.SIN,
CLI.SpouseName = TCP.spouseName,
CLI.SpouseSIN = TCP.SpouseSIN,
CLI.City = TCP.City,
CLI.LanguageID = TCP.LanguageId,
CLI.ClientName = TCP.LastName+' '+IsNull(TCP.FirstName,'')
FROM #TMP_CLIENTS_PROCESSING TCP,
VSNIAD..Clients CLI
WHERE
TCP.RecordId = @iNextRowId and
CLI.ClientId = TCP.ClientId and
ErrorCode = 0
[/font]
You can try leaving it alone for now, but I try to avoid it unless it's just too darned convenient for certain types of joins.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2008 at 10:11 pm
After you fix the updates and try the run (please let me know how that goes) again, then next thing I'd try to do is get rid of the While loops. It appears that you're doing an "UPSERT" or "Merge" of data. This should normally be done in a setbased fashion using Insert/Select and "Joined Updates". But, let's wait an see how the Update fixes work, first.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 15, 2008 at 12:14 pm
Hi Jeff,
First of all it's not my code.
I would never code like this.
It's a third party software we're using and
the vendor keeps telling us "it's your environment problems. tempdb size,
not following the instructions during installation,etc."
Usual stuff.
Jeff,
I think you are right.
I replaced UPDATE code and ran it.
But it now gets stuck inside a loop.
I run this SP as a script. I put lots of prints
and the last prints I get are:
While @a <=4..
@sql=Insert into vsNiad..ClientEmail(RowId,ClientId,Email)
SELECT 3, ClientId,Email3 FROm #TMP_CLENT_PROCESSING WHERE INSERTIND = 1 and ErrorCode = 0 and LEN(Email3) > 0
It never can get out of this LOOP:
WHILE @a<=4
BEGIN
print 'WHILE @a<=4..'
SELECT @Email =
(
SELECT CASE @a
when 1 then 'Email1'
when 2 then 'Email2'
when 3 then 'Email3'
when 4 then 'Email4'
END
)
SELECT @sql = 'INSERT INTO vsNiad..ClientEmail (RowId, ClientId, Email) '
SELECT @sql = @sql +' SELECT ' + cast(@a as varchar(1)) + ',ClientId,'+ @Email
SELECT @sql = @sql +' FROM #TMP_CLIENTS_PROCESSING WHERE INSERTIND = 1 and ErrorCode = 0 and '
SELECT @sql = @sql +'len('+ @Email+')>0'
EXEC (@sql)
print '@sql=' + @sql
SELECT @sql =''
SET @intErrorCode=@@error
if @intErrorCode <> 0
begin
INSERT INTO vsImport..import_log SELECT 'Clients-Emails...InvalidFields','ImportClients',@sql,0,0,0 ,getdate()
end
SET @a=@a+1
END
-- End of Emails INSERTS
print 'End of Email INSERTS'
Looks like these loops with dynamic SQL are killing CPU...?
July 15, 2008 at 1:26 pm
Guys,
my debugging prints get buffered a lot.
Is there any way to flush them?
July 15, 2008 at 1:45 pm
riga1966 (7/15/2008)
Hi Jeff,I think you are right.
I replaced UPDATE code and ran it.
But it now gets stuck inside a loop.
I run this SP a a script. I put lots of prints
and the last prints I get are:
While @a <=4..
@sql=Insert into vsNiad..ClientEmail(RowId,ClientId,Email)
SELECT 3, ClientId,Email3 FROm #TMP_CLENT_PROCESSING WHERE INSERTIND = 1 and ErrorCode = 0 and LEN(Email3) > 0
Looks like these loops with dynamic SQL are killing CPU...?
The dynamic SQL isn't killing anything... doing 1 or more inserts per row? Now that's a killer. Strange part is, you have the set based version of the insert in the code and, if it fails, you rollback and go to an insane amount of RBAR.
All the stuff you want is in a temporary staging table... make ALL the stuff 100% right before you even think of doing your UPSERT, and then do all the rows at once...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2008 at 12:25 pm
The issue has been resolved.
Apparently we were running Import process still in FULL INITIAL LOAD mode.
Clients table had 243,000 records and SQL code (I still don't like but I don't have mandate to change it)
was trying to do these massive updates using #TMP_PROCESSING_CLIENTS.
We were supposed to run Import process in DELTA LOAD mode.
Now imported Clients table has only 883 records
and it means #TMP_PROCESSING_CLIENTS has 883 records.
The process ran 2.5 min
CPU spikes were short.
My manager says not to spend more time on it.
I'm out of it.
Thanks for your help guys.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply