通过一个日期字段更新另一个日期字段,用游标处理怎么这么慢。
ALTER PROC ProcWeekANDQuarter
@monthVar DATETIME
AS
DECLARE @weekVar DATETIME
DECLARE week_Cursor CURSOR FOR
SELECT SellDate FROM basic WHERE SellMonth=@monthVar ORDER BY SellDate
OPEN week_Cursor
FETCH NEXT FROM week_Cursor INTO @weekVar
WHILE @@FETCH_STATUS=0
BEGIN
IF((day(@weekVar)>=2) AND (day(@weekVar)<=8))--第二周
UPDATE basic SET SellWeek =cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-2'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=9 AND day(@weekVar)<=15)--第三周
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-9'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=16 AND day(@weekVar)<=22)--第四周
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-16'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=23 AND day(@weekVar)<=25)--第五周
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-23'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=26 or day(@weekVar)<=1)--第一周
BEGIN
IF(day(@weekVar)>=26)
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-26'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)<=1)
BEGIN
IF(month(@weekVar)=1)
UPDATE basic SET SellWeek=cast((year(@weekVar)-1) AS VARCHAR)+'-12-26'
WHERE SellDate=@weekVar
ELSE
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast((month(@weekVar)-1)AS VARCHAR)+'-26'
WHERE SellDate=@weekVar
END
END
FETCH NEXT FROM week_Cursor INTO @weekVar
END
CLOSE week_Cursor--关闭游标
DEALLOCATE week_Cursor--删除游标
ALTER PROC ProcWeekANDQuarter
@monthVar DATETIME
AS
DECLARE @weekVar DATETIME
DECLARE week_Cursor CURSOR FOR
SELECT SellDate FROM basic WHERE SellMonth=@monthVar ORDER BY SellDate
OPEN week_Cursor
FETCH NEXT FROM week_Cursor INTO @weekVar
WHILE @@FETCH_STATUS=0
BEGIN
IF((day(@weekVar)>=2) AND (day(@weekVar)<=8))--第二周
UPDATE basic SET SellWeek =cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-2'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=9 AND day(@weekVar)<=15)--第三周
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-9'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=16 AND day(@weekVar)<=22)--第四周
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-16'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=23 AND day(@weekVar)<=25)--第五周
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-23'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)>=26 or day(@weekVar)<=1)--第一周
BEGIN
IF(day(@weekVar)>=26)
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast(month(@weekVar)AS VARCHAR)+'-26'
WHERE SellDate=@weekVar
ELSE IF(day(@weekVar)<=1)
BEGIN
IF(month(@weekVar)=1)
UPDATE basic SET SellWeek=cast((year(@weekVar)-1) AS VARCHAR)+'-12-26'
WHERE SellDate=@weekVar
ELSE
UPDATE basic SET SellWeek=cast(year(@weekVar)AS VARCHAR)+'-'+cast((month(@weekVar)-1)AS VARCHAR)+'-26'
WHERE SellDate=@weekVar
END
END
FETCH NEXT FROM week_Cursor INTO @weekVar
END
CLOSE week_Cursor--关闭游标
DEALLOCATE week_Cursor--删除游标
对于大于1w条记录以上,大部分情况下建议不使用游标。
楼主可以这样改进:
如把UPDATE basic SET SellWeek =cast(year(@weekVar)AS VARCHAR)+'-'+cast改成使用
SET @EXECUTE_SQL=@EXECUTE_SQL+'UPDATE ...'这样构造更新语句,再控制好LEN(@EXECUTE_SQL)不超过4000,当比较接近4000时,就调用EXECUTE(@EXECUTE_SQL)更新数据。这样也可以效率。