T-SQL Insert Update optimization - Cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL Insert Update optimization - Cursor

Author

Topic

ruruboy
Starting Member

1 Post

Posted - 2010-11-14 : 06:17:33

Please find below a stored procedure to process millions of records, where we insert or update. Is there a way I can optimize the code, so that it runs faster, without using a cursor. The stored procedure is given below. The issue is that I have to generate the auto incrementing keys. And based upon its existence in one table update a record in one table/insert a record into two tables. Kindly review and let me know. Thanks in advance.

USE Database GO

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[Procedure Name]

AS BEGIN

SET NOCOUNT ON;

DECLARE @voters_no varchar(18);
DECLARE @cp_voters_no varchar(18);
DECLARE @candidate_id char(10);
DECLARE @max_candidate_id char(10);
DECLARE @max_recNo as int;

DECLARE @lastName varchar(30);
DECLARE @firstName varchar(30);
DECLARE @address varchar(50);
DECLARE @city varchar(30);
DECLARE @state char(2);
DECLARE @zip varchar(9);
DECLARE @region varchar(30);
DECLARE @candId as char(10);
DECLARE @recNo as int;
DECLARE @defDate as datetime;

Select @defDate = CONVERT(DATETIME,'1900-01-01 00:00:00');

DECLARE JMS_CURSOR CURSOR LOCAL FAST_FORWARD FOR SELECT VOTERS_NO , LAST_NAME, FIRST_NAME, ADDRESS, CITY, STATE, ZIP, REGION FROM PROFILE_BC; OPEN JMS_CURSOR; FETCH NEXT FROM JMS_CURSOR INTO @voters_no, @lastName , @firstName, @address, @city, @state, @zip, @region;

SELECT @max_candidate_id = MAX(CANDIDATE_ID)FROM PROFILE WHERE CANDIDATE_ID NOT LIKE 'MA%'; SELECT @max_recNo = MAX(REC_NO) FROM SOURCE;

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @candidate_id = CP.CANDIDATE_ID, @cp_voters_no = CP.VOTERS_NO FROM PROFILE CP INNER JOIN SOURCE SR ON CP.CANDIDATE_ID = SR.CANDIDATE_ID WHERE CP.CANDIDATE_ID NOT LIKE 'MA%' AND CP.VOTERS_NO = @voters_no;

IF (@cp_voters_no = @voters_no) -- records exist in both profile, source and EBC tables BEGIN

UPDATE PROFILE SET LAST_NAME = @lastName, FIRST_NAME = @firstName , ADDRESS = @address, CITY = @city , STATE = @state , ZIP = @zip, REGION = @region WHERE CANDIDATE_ID = @candidate_id;

END
ELSE
BEGIN

SET @candId = RIGHT('0000000000' + CONVERT(varchar(10) ,@max_candidate_id + 1), 10); SET @max_candidate_id = @candId;

SET @recNo = 0;

INSERT INTO PROFILE (candidate_id , last_name, first_name, address, city, state, zip, region, dob, voters_no) VALUES ( @candId, @lastName, @firstName, @address, @city, @state, @zip, @region, @defDate, @voters_no) ;

SET @recNo = @max_recNo + 1;
SET @max_recNo = @recNo;

INSERT INTO SOURCE (candidate_id, rec_no, sum_date, qual_date, sum_date_mun, sum_date_rgj) VALUES ( @candId, @recNo, @defDate, @defDate, @defDate, @defDate );

END

SET @cp_voters_no = '';

FETCH NEXT FROM JMS_CURSOR INTO @voters_no, @lastName , @firstName, @address, @city, @state, @zip, @region;

END

CLOSE JMS_CURSOR;
DEALLOCATE JMS_CURSOR;
END

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-15 : 09:42:06

Assuming voters_no is unique, something like the following should work.
You may want to look at spliting the upserts into batches of 10,000 or so if concurrency or LOG file space are problems.


UPDATE P
SET Last_Name = B.LastName
	,First_Name = B.firstName
	,[Address] = B.[Address]
	,City = B.City
	,[State] = B.[State]
	,zip = B.zip
	,Region = B.region
FROM [Profile] P
	JOIN Profile_BC B
		ON P.Voters_No = B.Voters_No
WHERE P.Candidate_Id NOT LIKE 'MA%';

WITH MaxCand
AS
(
	SELECT MAX(CAST(Candidate_Id AS int)) AS max_candidate_id
	FROM PROFILE
	WHERE Candidate_Id NOT LIKE 'MA%'
)
INSERT INTO [Profile](candidate_id , last_name, first_name, [address], city, [state], zip, region, dob, voters_no)
SELECT REPLACE(STR(M.max_candidate_id + ROW_NUMBER() OVER (ORDER BY B.voters_no), 10), ' ', '0')
	,B.last_name, B.first_name, B.[address], B.city, B.[state], B.zip, B.region, 0, B.voters_no
FROM Profile_BC B
	CROSS JOIN MaxCand M;

WITH MaxRec
AS
(
	SELECT MAX(Rec_No) AS max_recNo
	FROM [Source]
)
INSERT INTO [Source] (candidate_id, rec_no, sum_date, qual_date, sum_date_mun, sum_date_rgj)
SELECT P.candidate_id
	,M.max_recNo + ROW_NUMBER() OVER (ORDER BY P.candidate_id)
	,0, 0, 0, 0
FROM [Profile] P
	CROSS JOIN MaxRec M
WHERE NOT EXISTS
(
	SELECT *
	FROM [Source] S
	WHERE S.candidate_id = P.candidate_id
);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources