CODEWITHSUNDEEP
sql-serverperformance
whd.nsr
whd.nsr

Reputation: 694

Optimization of SQL Server Query

My problem is that I created a query that takes too long to execute.

City   | Department  |  Employee   | Attendance Date | Attendance Status
------------------------------------------------------------------------
C1     | Dept 1      | Emp 1       | 2016-01-01      | ABSENT
C1     | Dept 1      | Emp 2       | 2016-01-01      | LATE
C1     | Dept 2      | Emp 3       | 2016-01-01      | VACANCY

So I want to create a view that contains same data and adds a column that contains the total number of employees (that serves me later in a SSRS project to determine the percentage of each status).

So I created a function that makes simple select filtering by department and date.

and this is the query that uses the function:

SELECT        City, Department, Employee, [Attendence Date], [Attendance Status], [Get Department Employees By Date](Department, [Attendence Date]) AS TOTAL
FROM            attendenceTable

This is the function:

CREATE FUNCTION [dbo].[Get Department Employees By Date]
(
    @deptID int = null,
    @date datetime = null
)
RETURNS nvarchar(max)
AS
BEGIN
    declare @result int = 0;
    select @result = count(*) from attendenceTable where DEPT_ID = @deptID and ATT_DATE_G = @date;
    RETURN @result;

END

The problem is that query takes too long (I mean very long time) to execute. Any Suggestion of optimization?

Upvotes: 1

Views: 66

Answers (1)

HeavenCore
HeavenCore

Reputation: 7683

Your function is a scalar function, which is run once for every row in the result set (~600,000) times, and is a known performance killer. It can be rewritten into an inline table-valued function, or if the logic is not required elsewhere, a simple group, count & join would suffice:

WITH    EmployeesPerDeptPerDate
          AS ( SELECT   DEPT_ID ,
                        ATT_DATE_G ,
                        COUNT(DISTINCT Employee) AS EmployeeCount
               FROM     attendenceTable
               GROUP BY DEPT_ID ,
                        ATT_DATE_G
             )
    SELECT  A.City ,
            A.Department ,
            A.Employee ,
            A.[Attendence Date] ,
            A.[Attendance Status] ,
            ISNULL(B.EmployeeCount, 0) AS EmployeeCount
    FROM    attendenceTable AS A
            LEFT OUTER JOIN EmployeesPerDeptPerDate AS B ON A.DEPT_ID = B.DEPT_ID
                                                            AND A.ATT_DATE_G = B.ATT_DATE_G;

Upvotes: 3

Related Questions