XML Datatype handling in SQL

Question

I have a requirement to insert/update bulk number of records into a table. For that, i have written a stored procedure as follows. But it is taking much time to execute. Could any one suggest changes to SP to get better performance in execution.

create  procedure sp_save_user
(
@a_i_lang_id        integer,
@a_s_data           ntext
)
WITH ENCRYPTION
as
begin   
set nocount on
SET QUOTED_IDENTIFIER ON 
--Declaring local variables
declare @l_s_USER_ID NVARCHAR(30)
declare @l_s_USER_NAME NVARCHAR(255)

declare @l_n_rmStatusCount numeric(10)
declare @l_n_XMLDoc XML

set @l_n_XMLDoc = cast(@a_s_data as xml)

CREATE TABLE #DW_TEMP_TABLE_SAVE(  
[USER_ID] [NVARCHAR](30), 
[USER_NAME] [NVARCHAR](255)
)   
insert into #DW_TEMP_TABLE_SAVE
select A.B.value('(USER_ID)[1]', 'nvarchar(30)' ) [USER_ID], 
A.B.value('(USER_NAME)[1]', 'nvarchar(30)' ) [USER_NAME]
from @l_n_XMLDoc.nodes('//ROW') as A(B) 

--Get total number od records
select @l_n_rmStatusCount = count(*) from #DW_TEMP_TABLE_SAVE

--loop through records and insert/update table
while (@l_n_rmStatusCount > 0)
begin
    SELECT  @l_s_USER_ID        =   [USER_ID] , 
            @l_s_USER_NAME          =   [USER_NAME]         
    FROM ( SELECT   ROW_NUMBER() OVER (ORDER BY [USER_ID]) AS rownumber,
        [USER_ID],[USER_NAME]  FROM #DW_TEMP_TABLE_SAVE) as temptablename
    WHERE rownumber = @l_n_rmStatusCount
    if exists(
    select 'X' from table_user_info(nolock)
    where [user_id]                 = @l_s_USER_ID      
    )
    begin
        -- do update 
    end
    else
    begin
        -- do insert 
    end     
    set @l_n_rmStatusCount = @l_n_rmStatusCount -1
end
drop table #DW_TEMP_TABLE_SAVE   
SET QUOTED_IDENTIFIER OFF       
set nocount off
end
go

Answer 1

Never EVER perform large amounts of INSERTS/UPDATES using a WHILE loop!!!

Use the T-SQL MERGE statement when you want to insert some records while updating other records at the same time. Take a look at the examples or come back here and ask for more specific questions if you have trouble getting it to work.

Answer 2

A few things. Get rid of the cursor. Use table variables instead of #temp types, bulk update/insert. Use xml data type out of the gate instead of converting it later on in the code

For example.

Input variables:

create  procedure sp_save_user
(
@a_i_lang_id        integer,
@a_s_data           xml
)

Table variable:

DECLARE @DW_TEMP_TABLE_SAVE AS TABLE (  
[USER_ID] [NVARCHAR](30), 
[USER_NAME] [NVARCHAR](255)
)  

Bulk insert:

INSERT INTO your_table (Column1,Column2)
select Column1,Column2 from @DW_TEMP_TABLE_SAVE where Column1 not in (select column1 from your_table) --Provided Column1 is the key, otherwise just do a left join and get records where the left joined table is null

Bulk update:

Update destination
set destination.Column1 = source.Column1, destination.Column2 = source.Column2
from your_table destination
inner join @DW_TEMP_TABLE_SAVE source on destination.Column1 = source.Column1 and destination.Column2 = source.Column2

The above methods show you how to insert records if they don't exist, update records that do exist from the source table (which is the changes), all without a cursor, which is quite heavy in processing

Hope this helps