Update data in SQL Table row by row

Question

I'm using SQL Server 2008 R2. I have a View [EmployeeMaster] from a Linked Server and a table [EmployeeDetails] in database. i have a service which run at 12 AM everyday calling a stored procedure which sync the table with view. ie, stored procedure check and update each row in [EmployeeDetails] with the matching row from view. My stored procedure goes like this.

CREATE PROCEDURE usp_OracleSyncUpdate
AS

CREATE TABLE #ActiveEmployees (
 RowID int IDENTITY(1, 1), 
 [EmployeeId] nvarchar(50),
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @EmployeeId nvarchar(50)
        ,@EmployeeName nvarchar(50)
        ,@EmployeeLastName nvarchar(50)
        ,@EmployeeCategory nvarchar(50)
        ,@ContactNo nvarchar(50)
        ,@Email nvarchar(50)
        ,@Gender nvarchar(50)
        ,@JoiningDate DATETIME

-- into the temporary table
INSERT INTO #ActiveEmployees ([EmployeeId])
SELECT [EMPLOYEE_NUMBER]
FROM [dbo].[EmployeeMaster]
WHERE [EMPLOYEE_NUMBER] IN (
        SELECT EmployeeId
        FROM [dbo].[EmployeeDetails]
        )

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @EmployeeId = EmployeeId
 FROM #ActiveEmployees
 WHERE RowID = @RowCount

 SELECT @EmployeeName = EmployeeName FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@EmployeeName)) <> (SELECT LTRIM(RTRIM([FIRST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET EmployeeName = (SELECT LTRIM(RTRIM([FIRST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @EmployeeLastName = EmployeeLastName FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@EmployeeLastName)) <> (SELECT LTRIM(RTRIM([LAST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET EmployeeLastName = (SELECT LTRIM(RTRIM([LAST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @EmployeeCategory = [Category] FROM [dbo].[EmployeeCategory] WHERE [EmployeeCategoryId] = (SELECT [EmployeeCategoryId] FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId)
 IF(LTRIM(RTRIM(@EmployeeCategory)) <> (SELECT LTRIM(RTRIM([JOB_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET [EmployeeCategoryId] = (SELECT [EmployeeCategoryId] FROM [dbo].[EmployeeCategory] WHERE [Category] = (SELECT LTRIM(RTRIM([JOB_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)) 
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @Email = Email FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@Email)) <> (SELECT LTRIM(RTRIM([EMAIL_ADDRESS])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET Email = (SELECT LTRIM(RTRIM([EMAIL_ADDRESS])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @Gender = Gender FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@Gender)) <> (SELECT LTRIM(RTRIM([GENDER])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET Gender = (SELECT LTRIM(RTRIM([GENDER])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @JoiningDate = JoiningDate FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@JoiningDate)) <> (SELECT LTRIM(RTRIM([DATE_OF_JOINING])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET JoiningDate = (SELECT LTRIM(RTRIM([DATE_OF_JOINING])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END

 SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #ActiveEmployees

Its working and it updates the columns. now the table has 26196 Records and it takes about 23:56 minutes to execute this stored procedure. In future the records will keep increasing and the execution time will be more.

Is there any way to bring down the execution time (suggest some optimization for the query )?
Or is there any other way i can sync the two table?
Or Is there any way to calculate the execution time for future also and i can set the connection timeout according to that?

Here is my final code

    alter PROCEDURE usp_OracleSyncUpdate
AS
    UPDATE ed
    SET ed.[EmployeeName] = LTRIM(RTRIM(em.FIRST_NAME)),
        ed.EmployeeLastName = LTRIM(RTRIM(em.LAST_NAME)), 
            ed.EmployeeCategoryId = ec.EmployeeCategoryId,
            ed.Email = isnull(ed.Email,em.[EMAIL_ADDRESS]),
            ed.[ContactNo] = isnull(ed.[ContactNo],em.ContactNo),
            ed.Gender = em.Gender,
            ed.JoiningDate = em.[DATE_OF_JOINING]
        FROM dbo.EmployeeDetails ed
        INNER JOIN [EmployeeMaster] em ON ed.EmployeeId = em.[EMPLOYEE_NUMBER]
        INNER JOIN dbo.EmployeeCategory ec ON ec.Category = em.[JOB_NAME]

Thanks @marc_s

Please change the title: Updating a super small table. Not even a million rows- and that is small. — TomTom
– TomTom, Commented Dec 2, 2014 at 8:50
One way to optimize would be to avoid RBAR (row-by-agonizing-row) processing, and use a set-based UPDATE statement instead... — marc_s
– marc_s, Commented Dec 2, 2014 at 8:51

marc_s · Accepted Answer · 2014-12-02 08:58:54Z

3

Get rid of the RBAR (row-by-agonizing-row) processing - relational databases work in sets of data - use a single set-based UPDATE statement and you're done.

Something along the lines of :

CREATE PROCEDURE usp_OracleSyncUpdate
AS
    CREATE TABLE #ActiveEmployees (
     RowID int IDENTITY(1, 1), 
     [EmployeeId] nvarchar(50),
    )

    -- into the temporary table
    INSERT INTO #ActiveEmployees ([EmployeeId])
      SELECT [EMPLOYEE_NUMBER]
      FROM [dbo].[EmployeeMaster]
      WHERE [EMPLOYEE_NUMBER] IN (SELECT EmployeeId
                              FROM [dbo].[EmployeeDetails])

    UPDATE dbo.EmployeeDetails
    SET ed.EmployeeName = LTRIM(RTRIM(ae.FIRST_NAME)),
        ed.EmployeeLastName = (SELECT LTRIM(RTRIM(ae.LAST_NAME)), 
            ed.EmployeeCategoryId = ec.EmployeeCategoryId,
            ed.Email = ae.Email,
            ed.Gender = ae.Gender,
            ed.JoiningDate = ae.JoiningDate
        FROM dbo.EmployeeDetails ed
        INNER JOIN #ActiveEmployees ae ON ed.EmployeeId = ae.EmployeeId
        INNER JOIN dbo.EmployeeCategory ec ON .......
        WHERE EmployeeId = @EmployeeId

I didn't know how the EmployeeCategory table is linked to the others - you'll need to add that JOIN condition on the INNER JOIN line there.

Also, quite possibly, you could also eliminate that temporary table altogether and just update EmployeeDetails directly from EmployeeMaster as a second step to save memory and processing time.

answered Dec 2, 2014 at 8:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sharon Over a year ago

Thanks for the quick response. I will implement this and let all know the result. Thank you.

TomTom · Accepted Answer · 2014-12-02 08:52:08Z

2

Well, instead of a loop do it in one sql statement and be done with it. Seriously.

I see a loop and I see a ton of indivisual statements and I can envision that being ONE UPATE STATEMENT ONLY.

On top, this is not "hugh table". It is tiny. Super tiny. One million rows is small. This has not even 27000 rows. You just abuse the server it by making a "Programmer approach" - loop, way too many statement. Use a set based approach and your whole loop can be written as ONE STATEMENT.

answered Dec 2, 2014 at 8:52

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Collectives™ on Stack Overflow

Update data in SQL Table row by row

Here is my final code

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Here is my final code

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related