SQL Cursor或While循环的替代方案？ [英] Alternative for SQL Cursor or While loop ?

问题描述

如果我使用 while循环（也使用游标）进入我的商店程序，在某些情况下更新超过100行，那么它会变慢，需要2分钟以上执行，有时创建 TimeOut 问题。请建议我选择Cursor / While循环。

请参考下面的场景。

If i use to "while loop(also used cursor)" into my store procedure for updation of more than 100 rows with some condition then it is going to slow, it take more than 2 min for execution and sometime create "TimeOut" problem. Please suggest me alternative of Cursor/While Loop.
Please refer below scenario for same.

CREATE Table #Temp 
(
  Ind INT IDENTITY(1,1),
  EmpID VARCHAR(50),
  DOJ DATE,
  SAL INT,
  Nationality VARCHAR(50)
)
DECLARE @MinIdt INT, @MaxIdt INT
INSERT INTO #Temp SELECT EMPID,DOJ,SAL FROM EMP --------- FOR SOME CONDITION
	DECLARE @MinIdt INT
    DECLARE @MaxIdt INT
SELECT @MinIdt = MIN(IDT), @MaxIdt = MAX(IDT) FROM  #Temp
	WHILE @MinIdt <= @MaxIdt
	BEGIN  
	 -- Check 1st Condition
	     --IF EXISTS 
	        --CHECK 2nd Condition
	  		   -- IF True 3rd Condition
	  		       --THEN 				
	  		          -- check Nationality for lets say Indian 4th Condition
	  		            --Update Emp table with condition
	  		             -- Update one more log table
	  		           --else   
	  		             --check for lets say NRI 
	  						 --Update Emp table with condition
	  						 -- Update one more log table
	  		                
    END 

推荐答案

当我们处理TSQL时，与逐行操作相比，它应该作为设置问题处理。



您可以为每个条件组合编写更新查询，可以一次更新多行。



您需要计算集合的内容而不是每行操作。或者，您可以在编程层（C＃）的DataSet中执行此操作，并在SQL中一次更新。

When we deal with TSQL, it should be approached as set problem in contrast to row by row operations.

You can write update query for each combination of condition, which may update multiple rows in one go.

You need to work out interms of set instead of each row operation. Alternatively, you can do this in DataSet in programming layer(C#) and update in one go in SQL.

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