SQL数据库--统计类型查询
在做统计类型的查询或数据获取时,应该先按照统计的类别先进行分组,然后查询。
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秒(
@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
修改存储过程如下:
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
(
@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也要看情况,不是任何情况下都适用,要具体情况具体分析了。