更新大数据量的效率问题

[ronanlin]

CREATE PROCEDURE sp_Attendance_UpdateArrangeWorkState
@StartDate nvarchar(50),
@EndDate nvarchar(50)
AS
BEGIN TRANSACTION
--更新正常上班打卡记录
--选择记录进行排序
CREATE TABLE #TempTable1
(
ID int IDENTITY PRIMARY KEY,
CardRecordID int,
AttendanceNo nvarchar(50),
EnterTime datetime,
PersonNo nvarchar(50)
)
INSERT INTO #TempTable1(CardRecordID,AttendanceNo,EnterTime,PersonNo)
SELECT DKJL.CardRecordID,DKJL.AttendanceNo,DKJL.EnterTime,RYJL.PersonNo
FROM AD_D_Data_A_DKJL DKJL
INNER JOIN AD_D_Data_A_RYJL RYJL
ON DKJL.AttendanceNo=RYJL.AttendanceNo
WHERE DKJL.Tag=0 ORDER BY DKJL.EnterTime ASC

UPDATE PBJL SET PBJL.State=1,PBJL.EnterTime=DKJL.EnterTime,PBJL.PersonNo2=DKJL.PersonNo
FROM AD_D_Data_A_PBJL PBJL,#TempTable1 DKJL
WHERE PBJL.PersonNo=DKJL.PersonNo
AND PBJL.WorkDate=CONVERT(char(10),DKJL.EnterTime,20)
AND PBJL.WorkTime>=CONVERT(char(8),DKJL.EnterTime,8)
AND DATEDIFF(minute, CONVERT(char(8),DKJL.EnterTime,8), PBJL.WorkTime)<=CONVERT(int,PBJL.EffectiveTime)
AND PBJL.WorkTag=0
AND CONVERT(char(10),DKJL.EnterTime,20)>=@StartDate
AND CONVERT(char(10),DKJL.EnterTime,20)<=@EndDate


--更新上班原始记录标识
UPDATE DKJL SET DKJL.Tag=1
FROM AD_D_Data_A_PBJL PBJL,AD_D_Data_A_DKJL DKJL,AD_D_Data_A_RYJL RYJL
WHERE DKJL.AttendanceNo=RYJL.AttendanceNo AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.WorkTag=0
AND PBJL.EnterTime= DKJL.EnterTime


--更新正常下班打卡记录
--选择记录进行排序
CREATE TABLE #TempTable2
(
ID int IDENTITY PRIMARY KEY,
CardRecordID int,
AttendanceNo nvarchar(50),
EnterTime datetime,
PersonNo nvarchar(50)
)
INSERT INTO #TempTable2(CardRecordID,AttendanceNo,EnterTime,PersonNo)
SELECT DKJL.CardRecordID,DKJL.AttendanceNo,DKJL.EnterTime,RYJL.PersonNo
FROM AD_D_Data_A_DKJL DKJL
INNER JOIN AD_D_Data_A_RYJL RYJL
ON DKJL.AttendanceNo=RYJL.AttendanceNo
WHERE DKJL.Tag=0 ORDER BY DKJL.EnterTime DESC

UPDATE PBJL SET PBJL.State=1,PBJL.EnterTime=DKJL.EnterTime,PBJL.PersonNo2=DKJL.PersonNo
FROM AD_D_Data_A_PBJL PBJL,#TempTable2 DKJL
WHERE PBJL.PersonNo=DKJL.PersonNo
AND PBJL.WorkDate=CONVERT(char(10),DKJL.EnterTime,20)
AND PBJL.WorkTime<=CONVERT(char(8),DKJL.EnterTime,8)
AND DATEDIFF(minute, PBJL.WorkTime, CONVERT(char(8),DKJL.EnterTime,8))<=CONVERT(int,PBJL.EffectiveTime)
AND PBJL.WorkTag=1
AND CONVERT(char(10),DKJL.EnterTime,20)>=@StartDate
AND CONVERT(char(10),DKJL.EnterTime,20)<=@EndDate


--更新上班原始记录标识
UPDATE DKJL SET DKJL.Tag=1
FROM AD_D_Data_A_PBJL PBJL,AD_D_Data_A_DKJL DKJL,AD_D_Data_A_RYJL RYJL
WHERE DKJL.AttendanceNo=RYJL.AttendanceNo AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.WorkTag=1
AND PBJL.EnterTime= DKJL.EnterTime

--更新不正常上班打卡记录
--选择记录进行排序
CREATE TABLE #TempTable3
(
ID int IDENTITY PRIMARY KEY,
CardRecordID int,
AttendanceNo nvarchar(50),
EnterTime datetime,
PersonNo nvarchar(50)
)
INSERT INTO #TempTable3(CardRecordID,AttendanceNo,EnterTime,PersonNo)
SELECT DKJL.CardRecordID,DKJL.AttendanceNo,DKJL.EnterTime,RYJL.PersonNo
FROM AD_D_Data_A_DKJL DKJL
INNER JOIN AD_D_Data_A_RYJL RYJL
ON DKJL.AttendanceNo=RYJL.AttendanceNo
WHERE DKJL.Tag=0 ORDER BY DKJL.EnterTime DESC

UPDATE PBJL SET PBJL.State=2,PBJL.EnterTime=DKJL.EnterTime,PBJL.PersonNo2=DKJL.PersonNo
FROM AD_D_Data_A_PBJL PBJL,#TempTable3 DKJL, AD_D_Data_A_GBAP GBAP
WHERE PBJL.PersonNo=DKJL.PersonNo
AND GBAP.WorkArrangeNo=PBJL.WorkArrangeNo
AND PBJL.WorkDate=CONVERT(char(10),DKJL.EnterTime,20)
AND CONVERT(char(8),DKJL.EnterTime,8)>GBAP.StartTime
AND CONVERT(char(8),DKJL.EnterTime,8)<GBAP.EndTime
AND PBJL.State=0
AND PBJL.WorkTag=0
AND CONVERT(char(10),DKJL.EnterTime,20)>=@StartDate
AND CONVERT(char(10),DKJL.EnterTime,20)<=@EndDate


--更新上班原始记录标识
UPDATE DKJL SET DKJL.Tag=1
FROM AD_D_Data_A_PBJL PBJL,AD_D_Data_A_DKJL DKJL,AD_D_Data_A_RYJL RYJL
WHERE DKJL.AttendanceNo=RYJL.AttendanceNo AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.EnterTime= DKJL.EnterTime
AND PBJL.WorkTag=0

--更新不正常下班打卡记录
--选择记录进行排序
CREATE TABLE #TempTable4
(
ID int IDENTITY PRIMARY KEY,
CardRecordID int,
AttendanceNo nvarchar(50),
EnterTime datetime,
PersonNo nvarchar(50)
)
INSERT INTO #TempTable4(CardRecordID,AttendanceNo,EnterTime,PersonNo)
SELECT DKJL.CardRecordID,DKJL.AttendanceNo,DKJL.EnterTime,RYJL.PersonNo
FROM AD_D_Data_A_DKJL DKJL
INNER JOIN AD_D_Data_A_RYJL RYJL
ON DKJL.AttendanceNo=RYJL.AttendanceNo
WHERE DKJL.Tag=0 ORDER BY DKJL.EnterTime ASC

UPDATE PBJL SET PBJL.State=2,PBJL.EnterTime=DKJL.EnterTime,PBJL.PersonNo2=DKJL.PersonNo
FROM AD_D_Data_A_PBJL PBJL,#TempTable4 DKJL, AD_D_Data_A_GBAP GBAP
WHERE PBJL.PersonNo=DKJL.PersonNo
AND GBAP.WorkArrangeNo=PBJL.WorkArrangeNo
AND PBJL.WorkDate=CONVERT(char(10),DKJL.EnterTime,20)
AND CONVERT(char(8),DKJL.EnterTime,8)>GBAP.StartTime
AND CONVERT(char(8),DKJL.EnterTime,8)<GBAP.EndTime
AND PBJL.State=0
AND PBJL.WorkTag=1
AND CONVERT(char(10),DKJL.EnterTime,20)>=@StartDate
AND CONVERT(char(10),DKJL.EnterTime,20)<=@EndDate


--更新下班原始记录标识
UPDATE DKJL SET DKJL.Tag=1
FROM AD_D_Data_A_PBJL PBJL,AD_D_Data_A_DKJL DKJL,AD_D_Data_A_RYJL RYJL
WHERE DKJL.AttendanceNo=RYJL.AttendanceNo AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.PersonNo=RYJL.PersonNo
AND PBJL.EnterTime= DKJL.EnterTime
AND PBJL.WorkTag=1



COMMIT TRANSACTION

GO

以上是更新考勤数据的存储过程，分4次筛选数据，上午上班，上午下班，下午上班，下午下班，
请问有没有执行效率高的方法？
多谢！

打卡记录表 AD_D_Data_A_DKJL
列名 　　　　数据类型 长度 允许空 备注 　　　　　　　　描述
CardRecordID int 4 主键 　　　　　　　　ID
AttendanceNo nvarchar 50 111130001 　　　　　　　　考勤编号
EnterTime 　　　　　datetime 8 2005-3-15 08:52:00　　　　打卡时间
MachineNumber nvarchar 50 1　　　　　　　　　　　　设备编号
InOutMode　　 nvarchar 50 　　　　　I 　　　　　　　　出入状态
Tag 　　　　　int 4 0默认值，1已筛选 　　　筛选标识

[vivianfdlpw]

1。 用表变量代替临时表
2。 你的事务处理没有用
要么在begin tran 前边加上set xact_abort on
要么用if @@error=0
begin
commit tran
end
else
begin
rollback tran
raiserror('错误信息',12,1)
end

[LGame]

来曾分的

[iwl]

用表变量代替临时表

[ronanlin]

up

[ronanlin]

没人回答啊!!!!!

[MorningTea]

如果在一个sp里面用临时表，也没有什么不好，起码他比表变量好的地方就是可以随时改变表结构，
而且局部临时表，不用担心并发问题，session结束，自动释放！

把update,insert等等会产生更新锁，排他锁的code包在trasaction里面，每做一步都设置个点，if @@error <> 0就goto到出错处理，rollback并且返回错误信息！

[hot3000]

up

[zhangyang555]

up

[sayhi]

那楼主的实际问题还是没有解决是不是，我也是在做这方面的事情，网上没有查出解决的方法。我们可以讨论一下QQ：12564178

[zxbyhcsdn]

数据海量，再怎么优化都慢。

[zxbyhcsdn]

最好优化设计和业务逻辑