sql计数性能问题

Question

我有两个表用于存储员工出勤信息。

一个表存储emp Id以及相应的时间和日期时间信息。第二个表存储其他员工详细信息，如员工Id、员工姓名等。。。我需要生成一份报告，显示emp每天工作的总小时数，一个状态列存储详细信息，如Present if total hours

我写了查询来获取每一个细节，但性能是不可接受的，需要大约30-35分钟来获取所有细节

如果排除天数计算逻辑，大约需要1-2分钟

表的结构是

Ist Employee表EmployeeID，EmployeeName。。。。。其他细节（目前不需要）

出席表

Emp\u ID，INOUT\u时间

我的问题

DECLARE @currStartDate DATETIME
DECLARE @currEndDate DATETIME
declare @startDate datetime;
declare @endDate datetime;

set @startDate = CONVERT(Datetime, '12/16/2013');
set @endDate = CONVERT(Datetime, '01/16/2014');

SET @currStartDate=@startDate
SET @currEndDate=dateAdd(day,1,@startDate)

DECLARE @formatTable TABLE
        (
            EmployeeCode varchar(10),
            EmployeeName varchar(100),
            [Date] Datetime,
            InTime datetime,
            OutTime datetime,
            TotalHrs varchar(10),
            [Status] varchar(10)
        )

WHILE @currEndDate <= @endDate
BEGIN
--get the day by day attendance Range
INSERT INTO @formatTable 
(
    EmployeeCode,
    EmployeeName,
    [Date],
    InTime,
    OutTime
)

SELECT
     E.EmployeeID,
    ISNULL(LTRIM(RTRIM(E.FirstName)),'') +' '+ISNULL(LTRIM(RTRIM(E.LastName)),'') AS EmployeeName,
    @currStartDate,
    MIN(AD.INOUT_Time)  as INTIME,
    CASE WHEN MAX(AD.INOUT_Time)=MIN(AD.INOUT_Time) THEN NULL ELSE  MAX(AD.INOUT_Time) END  as OUTTIME
    FROM employees E WITH(NOLOCK)
    LEFT OUTER JOIN Attendance AD
         ON E.EmployeeID = AD.Emp_ID
         AND INOUT_Time BETWEEN @currStartDate AND @currEndDate
        GROUP BY E.EmployeeID,DATEADD(dd, 0, DATEDIFF(dd, 0, INOUT_Time )) 

       UPDATE @formatTable

       SET TotalHrs=Convert(varchar(20),DATEDIFF(MINUTE, ISNULL(InTime,GETDATE()),ISNULL(OutTime,InTime))/Convert(decimal(4,2),60))    
        ,[Status] =(CASE WHEN DATEDIFF(MINUTE, ISNULL(InTime,GETDATE()),ISNULL(OutTime,InTime))/Convert(decimal(4,2),60) >= 4.5
                        THEN 'P'                           
                        ELSE 'Abs' END )
        ,HoursStatus = (CASE WHEN DATEDIFF(MINUTE, ISNULL(InTime,GETDATE()),ISNULL(OutTime,InTime))/Convert(decimal(4,2),60) >= 8.5 
                        THEN 'Greater Than 8.5'
                             WHEN DATEDIFF(MINUTE, ISNULL(InTime,GETDATE()),ISNULL(OutTime,InTime))/Convert(decimal(4,2),60) BETWEEN 6 AND 8.49
                        THEN '6-8.49'
                             WHEN DATEDIFF(MINUTE, ISNULL(InTime,GETDATE()),ISNULL(OutTime,InTime))/Convert(decimal(4,2),60) BETWEEN 4.5 AND 5.99
                        THEN '4.5-5.99' end)

        WHERE [Date]=@currStartDate 
                   -- moving to nextday
       SELECT @currStartDate=DATEADD(DAY,1,@currStartDate)
       SELECT @currEndDate=DATEADD(DAY,1,@currEndDate) 
 END

     IF OBJECT_ID('tempdb..##output') IS NOT NULL
        DROP TABLE ##output

     SELECT EmployeeCode,EmployeeName,[Date],Convert(varchar(10),INTime,108) INTime,Convert(varchar(10),Outtime,108) Outtime,TotalHrs,[Status],Convert(varchar,@startDate,105) as StartDate,Convert(varchar,@endDate,105) as EndDate, (SELECT COUNT(*) FROM @formatTable counter 
           WHERE ft.EmployeeCode = counter.EmployeeCode AND counter.[Status] = 'P' ) AS TotalPresent, (SELECT COUNT(*) FROM @formatTable counter 
           WHERE ft.EmployeeCode = counter.EmployeeCode AND counter.HoursStatus  = 'Greater Than 8.5' ) as gt8point5,(SELECT COUNT(*) FROM @formatTable counter 
           WHERE ft.EmployeeCode = counter.EmployeeCode AND counter.HoursStatus  = '6-8.49' ) as gt6lessthan8,(SELECT COUNT(*) FROM @formatTable counter 
           WHERE ft.EmployeeCode = counter.EmployeeCode AND counter.HoursStatus  = '4.5-5.99' ) as gt4point5lessthan6
     INTO ##output FROM @formatTable as ft
     GROUP BY EmployeeCode,EmployeeName,[Date],TotalHrs,INTime,Outtime,[Status]

     SELECT * FROM ##output AS AttendanceReport

是否有改进性能的建议，尤其是计日逻辑

Answer 1

快速查看您的查询，建议使用以下索引。

-- Assuming you don't have EmployeeID as a clustered index
CREATE INDEX IX_Employees_EmployeeID 
             ON Employees (EmployeeID)
                  INCLUDE (FirstName, LastName)

CREATE INDEX IX_Attendance_EmployeeID_INOUTTime
             ON Attendance (EmployeeID, INOUT_Time)

您还可以为@formatTable创建索引，但这取决于查询计划的状态以及生成的行数。