CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2008-r2
Sharon
Sharon

Reputation: 765

Update data in SQL Table row by row

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.

  1. Is there any way to bring down the execution time (suggest some optimization for the query )?
  2. Or is there any other way i can sync the two table?
  3. 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

Upvotes: 3

Views: 11597

Answers (2)

marc_s
marc_s

Reputation: 754538

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.

Upvotes: 3

TomTom
TomTom

Reputation: 62101

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.

Upvotes: 2

Related Questions