Hello all,

I have a store procedure where i have one sub query to return no . of attendance between period selection 

basically like this,

SELECT EmployeeCode,COUNT(DISTINCT ATTENDANCE_DATE)AS Mkt_Attendance 
FROM [dbo].[View_Marketing_Person_Attendance] 
WHERE CONVERT(DATE,ATTENDANCE_DATE) BETWEEN @from AND @to
GROUP BY EmployeeCode

@from and @to parameters passed to procedure and used in sub query to filter attendance of period of employees 

and i have join that query to main table in procedure using a EmployeeCode and displaying 

,SUM(DISTINCT Mkt_Attendance) AS Mkt_Attendance in Main select area

(using sum because having grouping summarize query )

Now , above query is run very fast as it use index seek on Attendance_date based in view defination of a log table and returns only 265 rows for period 2015-04-01 and 2015-06-30 in 0 sec. approx.

now when i am running a procedure for 2015-04-01 and 2015-06-30 it executes in 11 sec. after commenting a sub query 

and when i removed a comment and executes it takes 32 sec. but a strange is in statistics it shows 

Table 'Worktable'. Scan count 93, logical reads 6576643, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

worktable only shows when i use above sub query as it is because of the sub query i know.

please , help why the work table is showing high reads and is slowing query this much .

There can be a number of reasons that contribute to the need for a worktable and the high number of associated reads. 

Is the data type of ATTENDENCE_DATE datetime?  If so, consider specifying an inclusive from date and exclusive end date like the example below.  That will avoid the conversion for each row and allow more efficient use of an index on ATTENDENCE_DATE, of one exists. 

 SELECT EmployeeCode,COUNT(DISTINCT ATTENDANCE_DATE) AS Mkt_Attendance 
 FROM [dbo].[View_Marketing_Person_Attendance] 
 WHERE ATTENDANCE_DATE >= @from AND ATTENDANCE_DATE < DATEADD(day, 1, @to)
 GROUP BY EmployeeCode;

It may asp help if you have a composite index on EmployeeCode and ATTENDANCE_DATE.  I don't know if that's possible without knowing the underlying view query.  Can you post the view DDL along with tables and indexes?

Hi Dilip,

A few things to consider:

1. You only posted the subquery so there isn't much to go on.
2. If the subquery is your predicate for the main query then commenting it out is certainly going to kill the performance. Keep in mind that certain things can cause the data to spill to tempdb, such as: HASH JOIN operators, SORT operators, and SPOOL operators. There are more but those are the main ones I can think of right at the moment. Each of those could make a worktable.
3. In some cases referential integrity (trusted FKeys) allow SQL to entirely skip tables that you have included your query.
4. You could post your query plan and the output of set statistics io on and we could probably help further.

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)