替代SQL中的WHILE循环 [英] Alternative to WHILE loop in SQL

问题描述

我必须处理SP中的大量记录。为此，我正在使用WHILE循环，如下面的SP。但是，执行需要更多时间。请通过建议可以改善性能的其他替代方法来帮助我。提前致谢。

Hi,
I have to handle bulk number of records in SP. For that, i am using WHILE loop as in the following SP. But, it is taking more time to execute. Please help me by suggesting other alternate approach which can improve the performance. Thanks in advance.

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
        -- call another SP to do UPDATE multiple tables 
    end
    else
    begin
        -- call another SP to do INSERT multiple tables
    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

推荐答案

在SQL中循环槽记录的正确方法是使用 CURSOR 像这样：

The proper way of looping trough records in SQL is using a CURSOR like this:

DECLARE mycursor CURSOR FOR
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) 

DECLARE @USER_ID NVARCHAR(30) 
DECLARE @USER_NAME NVARCHAR(255)

OPEN mycursor
FETCH NEXT FROM mycursor INTO @USER_ID, @USER_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
  -- do your thing here

  FETCH NEXT FROM mycursor INTO @USER_ID, @USER_NAME
END

CLOSE mycursor
DEALLOCATE mycursor

我会建议你读一下MERGE 声明。也许你可以使用它重写整个循环，包括插入和更新。

I would further suggest you read about the MERGE statement. Perhaps you can rewrite the whole loop including the inserts and updates using that.

我建​​议使用CTE [ ^ ]。



公用表格式 [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

优化递归CTE查询 [ ^ ]

CTE In SQL Server [ ^ ]

I'd suggest to use CTE[^].

Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
Optimize Recursive CTE Query[^]
CTE In SQL Server[^]

这篇关于替代SQL中的WHILE循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！