优化一个考勤的SQL

[baiqukqdp]

最近在做一个生成考勤报表，对写的SQL语句速率感觉不满意。
表结构如下：

CREATE TABLE [Attendance]
(
[usercode] [dbo].[varchar](30) NOT NULL,
[logdate] [datetime] NOT NULL,
[mac] [dbo].[varchar](30),
[tag1] [char](1),
[tag2] [char](1),
[tag3] [char](1)
)
GO
CREATE TABLE [AttendanceTime]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[ontime] [dbo].[varchar](30) NULL,
[offtime] [dbo].[varchar](30) NULL,
[iscurrent] [bit] NULL
)GO

AttendanceTime的数据：
id ontime offtime iscurrent
1 DOY 08:00:00 DOY 17:30:00 1
意思为上班时间08：00,下班时间17：30

Attendance是从文本文件BULK INSERT过来的。
文本文件原始数据为：
......
01992 20100825173022 05 1 0 2
01888 20100825173024 05 1 0 2
01888 20100825173024 05 1 0 2
20 05 1 0 2
02245 20100825173027 05 1 0 2
02285 20100825173028 05 1 0 2
......
文本文件修正后数据为：
......
01992 2010-08-25 17:30:22 05 1 0 2
01888 2010-08-25 17:30:24 05 1 0 2
01888 2010-08-25 17:30:24 05 1 0 2
02245 2010-08-25 17:30:27 05 1 0 2
02285 2010-08-25 17:30:28 05 1 0 2
......

开始插入语句：
BULK INSERT HrcmsDemo.dbo.Attendance
FROM 'C:\Att20100903.After.txt'
WITH (FIELDTERMINATOR ='\t')
GO
你可以从这里下载到此文件
我写的SQL：

WITH TotalAttendance AS(
	SELECT usercode ,ontime ,CASE WHEN ontime=offtime THEN NULL ELSE offtime END offtime,COUNT(1) AS 'Count'
	FROM (
		SELECT a.usercode ,
			(SELECT MIN(a1.logdate) FROM Attendance a1 WHERE a1.usercode=a.usercode 
				AND Year(a1.logdate)=Year(a.logdate) AND Month(a1.logdate)=Month(a.logdate) AND DAY(a1.logdate)=DAY(a.logdate)
			) ontime ,
			(SELECT MAX(a2.logdate) FROM Attendance a2 WHERE a2.usercode=a.usercode 
				AND Year(a2.logdate)=Year(a.logdate) AND Month(a2.logdate)=Month(a.logdate) AND DAY(a2.logdate)=DAY(a.logdate)
			) offtime
		FROM (SELECT usercode ,logdate FROM Attendance WHERE YEAR(logdate)='2010' and MONTH(logdate)='9' GROUP BY usercode ,logdate) AS a
	) tt
	GROUP BY usercode, ontime ,offtime
)
INSERT INTO ttt(usercode ,workday ,timeon ,timeoff ,ontime ,offtime)
SELECT usercode,
	CONVERT(VARCHAR(10),h.ontime,120) workday,
	CAST(REPLACE(a.ontime,'DOY',CONVERT(VARCHAR(10),h.ontime,120)) AS DATETIME) timeon ,
	CAST(REPLACE(a.offtime,'DOY',CONVERT(VARCHAR(10),h.ontime,120)) AS DATETIME) timeoff ,h.ontime,h.offtime 
FROM TotalAttendance h LEFT JOIN AttendanceTime a ON 1=1;

DECLARE @ont CHAR(5),@offt CHAR(5);
SET @ont='08:00'; SET @offt='17:30';
SELECT ''''+UserName UserName ,
''''+t.usercode usercode ,''''+workday workday,
''''+timeon timeon,''''+ontime ontime,
''''+timeoff timeoff,''''+offtime offtime ,
early ,late ,
CASE WHEN ontime IS NULL OR offtime IS NULL THEN '1' WHEN COALESCE(early,late,0) = 0 THEN '0' ELSE '1' END 'state'
FROM ( 
	SELECT usercode ,workday ,@ont timeon ,dbo.fn_ParseTime(ontime) ontime ,@offt timeoff ,dbo.fn_ParseTime(offtime) offtime ,early ,late FROM ttt 
) t 
JOIN UserInfo AS ui ON t.usercode = ui.UserCode
ORDER BY t.usercode ,t.workday ;

[永生天地]

没怎么看懂。不过是考勤系统，建议每个月把完成的数据备份到历史里

[baiqukqdp]

此代码在没有重复数据的时候，生成一个月的考勤数据时间是8秒。
万望各位高手帮忙解答解答。

SQL 代码中的 ''''+ 只是为了导出EXCEL的时候转换为文本类型，保持 员工号00005 日期 2010-09-10 等等的形式。

还少了一个表结构

CREATE TABLE ttt
(
	id INT IDENTITY(1,1),
	usercode CHAR(5),
	workday CHAR(10),
	timeon DATETIME,
	timeoff DATETIME,
	ontime DATETIME,
	offtime DATETIME,
	early AS ( CASE WHEN DATEDIFF(n,timeon,ontime)<=0 THEN NULL ELSE DATEDIFF(n,timeon,ontime) END ),
	late AS ( CASE WHEN DATEDIFF(n,offtime,timeoff)<=0 THEN NULL ELSE DATEDIFF(n,offtime,timeoff) END )
)

缺少USERINFO表可以不用。

[sql_cctv]

看的眼花缭乱的

[baiqukqdp]

[Quote=引用 1 楼 xys_777 的回复:]
没怎么看懂。不过是考勤系统，建议每个月把完成的数据备份到历史里
[/Quote]
哪里看不懂？
这个考勤系统其实是 导入考勤机的数据，再生成员工的考勤统计。

[baiqukqdp]

再次人工置顶。

[dawugui]

考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.

[baiqukqdp]

[Quote=引用 6 楼 dawugui 的回复:]
考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.
[/Quote]
这个是，其实我在这里问的就是如何的没有索引的表快速地查找数据。

后期还是要在程序中作补充等等的工作的。
谢谢帮顶。

[--小F--]

[Quote=引用 7 楼 baiqukqdp 的回复:]
引用 6 楼 dawugui 的回复:
考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.

这个是，其实我在这里问的就是如何的没有索引的表快速地查找数据。

后期还是要在程序中作补充等等的工作的。
谢谢帮顶。
[/Quote]

还是建议用索引 虽然牺牲了配置 但是可以明显的提高查询速度

[baiqukqdp]

[Quote=引用 8 楼 fredrickhu 的回复:]
引用 7 楼 baiqukqdp 的回复:
引用 6 楼 dawugui 的回复:
考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.

这个是，其实我在这里问的就是如何的没有索引的表快速地查找数据。

后期还是要在程序中作补充等等的工作的。
谢谢帮顶。


还是建议用索引 虽然牺牲了配置 但是可以明显的提高查询速度
[/Quote]

考勤数据是从文本导入的，用的是BULK INSERT，好像没有办法配置表的列