在做统计类型的查询或数据获取时,应该先按照统计的类别先进行分组,然后查询。
ALTER PROCEDURE [dbo].[Workload_GetEmployeeWorkload]
(
@DepartmentId
int
,
@StartTime datetime,
@EndTime datetime
)
AS
SELECT
HumanResource_Records.RecordId,
dbo.getEmployeeName(HumanResource_Records.RecordId)
as
Employee,
dbo.getSumWorkHours(HumanResource_Records.RecordId,
44
,@StartTime,@EndTime)
as
WorkHours,
dbo.getSumAvailableOverTimeHours(HumanResource_Records.RecordId,
44
,@StartTime,@EndTime)
as
OverTimeHours
FROM
HumanResource_Records,
Project_EntryLog
WHERE
HumanResource_Records.DepartmentId
=
@DepartmentId
AND HumanResource_Records.RecordId
=
Project_EntryLog.RecordID
AND Project_EntryLog.WorkDate
>=
@StartTime
AND Project_EntryLog.WorkDate
<=
@EndTime
AND Project_EntryLog.ReportStatusID
!=
0
以上代码当库中有7000多条记录时,获取数据用时16秒
修改存储过程如下:
ALTER PROCEDURE [dbo].[Workload_GetEmployeeWorkload]
(
@DepartmentId
int
,
@StartTime datetime,
@EndTime datetime
)
AS
SELECT
HumanResource_Records.RecordId,
dbo.getEmployeeName(HumanResource_Records.RecordId)
as
Employee,
dbo.getSumWorkHours(HumanResource_Records.RecordId,
44
,@StartTime,@EndTime)
as
WorkHours,
dbo.getSumAvailableOverTimeHours(HumanResource_Records.RecordId,
44
,@StartTime,@EndTime)
as
OverTimeHours
FROM
HumanResource_Records,
Project_EntryLog
WHERE
HumanResource_Records.DepartmentId
=
@DepartmentId
AND HumanResource_Records.RecordId
=
Project_EntryLog.RecordID
AND Project_EntryLog.WorkDate
>=
@StartTime
AND Project_EntryLog.WorkDate
<=
@EndTime
AND Project_EntryLog.ReportStatusID
!=
0
group by HumanResource_Records.RecordId
仅仅增加了一句group by HumanResource_Records.RecordId,但耗费时间却近于0。
在用group by分组后,会先按照RecordId分组,然后查询,每个RecordId只查询一次,而不分组查询时却是只要记录中有的RecordId就进行查询,耗费更多的时间。当然使用group by也要看情况,不是任何情况下都适用,要具体情况具体分析了。