locked
Worktable showing high logical reads RRS feed

  • Question

  • 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 .


    Dilip Patil..


    • Edited by Shanky_621MVP Monday, July 6, 2015 6:08 AM formatting
    Saturday, July 4, 2015 9:18 AM

Answers

  • 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?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 5, 2015 2:05 AM
    Answerer
  • 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. :)

    Sunday, July 5, 2015 4:01 AM
  • Hi Dilip,

    Thanks for posting more information. I think there are a few things to consider:

    • The query plan shows both a sort and hash operator. Both of these can create objects in tempdb (work tables / files)
    • You have quite a bit of scans and reads. I don't see that shown in the graphical plan you posted but it also looks like that plan is not the whole story. I would look over the query and eliminate the converts. They are very bad for performance and you should be storing the data in the way that you plan to pull it out. Lines like "WHERE CONVERT(DATE,ATTENDANCE_DATE) BETWEEN @From_Date and @To_Date GROUP BY EmployeeCode )AS tbl_Attendance  ON CONVERT(INT,tbl_Attendance.EmployeeCode)=EMPLOYEE_CODE" are very bad. Never convert columns that are used in the predicate of a WHERE or JOIN. This can force SQL Server to scan the data instead of seeking it and in turn wreck performance.

    Clean up these things and the query should run much better.

    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. :)

    Monday, July 6, 2015 5:53 AM

All replies

  • 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?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 5, 2015 2:05 AM
    Answerer
  • 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. :)

    Sunday, July 5, 2015 4:01 AM
  • Thanks Dan and Daniel for the reply .. 

    @Dan 

    here is the view DDL for ref.

    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    GO


    CREATE VIEW [dbo].[View_Marketing_Person_Attendance] 
    AS


    SELECT DISTINCT EmployeeCode,CONVERT(DATE,ESSL.dbo.DeviceLogs.LogDate) AS ATTENDANCE_DATE
    FROM ESSL.dbo.DeviceLogs
    INNER JOIN ESSL.dbo.Employees ON ESSL.dbo.Employees.EmployeeCodeInDevice=ESSL.dbo.DeviceLogs.UserId
    GROUP BY EmployeeCode, CONVERT(DATE,ESSL.dbo.DeviceLogs.LogDate)

    GO

    and the above ask sub query  plan and statistics as follows ..

    sub query ..

    SELECT EmployeeCode,COUNT(DISTINCT ATTENDANCE_DATE)AS Mkt_Attendance 
    FROM [dbo].[View_Marketing_Person_Attendance] 
    WHERE CONVERT(DATE,ATTENDANCE_DATE) 
    BETWEEN '2015-04-01' AND '2015-06-30' 
    GROUP BY EmployeeCode

    Stats ..

    (249 row(s) affected)
    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'DeviceLogs'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Employees'. Scan count 1, logical reads 61, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Plan ..


    Dilip Patil..

    Monday, July 6, 2015 4:44 AM
  • Please have a look at my main query in store procedure ..



    SET STATISTICS IO ON

    SET NOCOUNT ON;

    DECLARE @From_Date DATETIME='2015-04-01'
    ,@To_Date DATETIME='2015-06-30'
    ,@SALES_DIVISION_IDs INT =2

    SELECT ISNULL(UMTM.USER_ID,USER_SM.USER_ID)AS UMTM_ID,ISNULL(UMTM.USER_NAME,USER_SM.USER_NAME) AS UMTM_USER
    ,dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID,dbo.USER_MASTER.USER_ID,dbo.USER_MASTER.USER_NAME + (CASE WHEN COALESCE(USER_MASTER.STATUS,0)=0 THEN ' (Left)' ELSE '' END )AS USER_NAME
    ,dbo.SALES_FOLLOWUP_TRANSCTION.CREATED_BY

    ,ISNULL(SUM(tbl_Intake.No_Of_Approved),0) AS No_Of_Approved
    ,CF_Intake.CF_Intake AS CF_Intake
    ,ISNULL(TBL_Inward.No_of_FilesInward,0) AS No_of_FilesInward
    ,ISNULL(TBL_APP.Firm_Category,'Blank')AS Firm_Category
    ,TBL_APP.No_of_ClientInward AS No_of_ClientInward
    ,ISNULL(TBL_APP.LOAN_AMOUNT,0) AS LOAN_AMOUNT
    ,Mkt_Attendance AS Mkt_Attendance


    FROM dbo.LEAD_POOL_DETAIL WITH(NOLOCK)
    INNER JOIN dbo.LEAD_POOL_TRANSACTION WITH(NOLOCK)ON dbo.LEAD_POOL_DETAIL.LEAD_ID = dbo.LEAD_POOL_TRANSACTION.LEAD_ID AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID =@SALES_DIVISION_IDs
    INNER JOIN dbo.SALES_FOLLOWUP_TRANSCTION WITH(NOLOCK)ON dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID= dbo.SALES_FOLLOWUP_TRANSCTION.LEAD_DETAIL_ID 
    INNER JOIN dbo.USER_MASTER WITH(NOLOCK)ON USER_ID = dbo.SALES_FOLLOWUP_TRANSCTION.CREATED_BY 
    INNER JOIN  dbo.MARKETING_PERSON_MASTER WITH(NOLOCK)ON dbo.USER_MASTER.USER_ID=dbo.MARKETING_PERSON_MASTER.USER_ID
    INNER JOIN dbo.EMPLOYEE_MASTER WITH(NOLOCK)ON dbo.EMPLOYEE_MASTER.USER_ID=dbo.MARKETING_PERSON_MASTER.USER_ID
    INNER JOIN dbo.SALES_DIVISION_USER_MAPPER WITH(NOLOCK)ON dbo.MARKETING_PERSON_MASTER.USER_ID = dbo.SALES_DIVISION_USER_MAPPER.USER_ID AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID=dbo.SALES_DIVISION_USER_MAPPER.DIVISION_ID AND ROLE_ID=3
    LEFT OUTER JOIN (SELECT EmployeeCode,COUNT(DISTINCT ATTENDANCE_DATE)AS Mkt_Attendance FROM [dbo].[View_Marketing_Person_Attendance] WHERE CONVERT(DATE,ATTENDANCE_DATE) BETWEEN @From_Date and @To_Date GROUP BY EmployeeCode )AS tbl_Attendance  ON CONVERT(INT,tbl_Attendance.EmployeeCode)=EMPLOYEE_CODE
    LEFT OUTER JOIN 
    (
    SELECT SALES_DIVISION_ID,dbo.LEAD_POOL_DETAIL.ALLOCATED_TO,COUNT(DISTINCT dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID) AS No_Of_Approved
    FROM  dbo.LEAD_STAGE_LOG WITH(NOLOCK)
    INNER JOIN  dbo.LEAD_POOL_DETAIL WITH(NOLOCK)ON dbo.LEAD_STAGE_LOG.LEAD_DETAIL_ID = dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID AND LEAD_STAGE_ID=60 AND dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID >0
    LEFT OUTER JOIN  dbo.LEAD_STAGE_LOG AS Approved ON Approved.LEAD_DETAIL_ID = LEAD_POOL_DETAIL.LEAD_DETAIL_ID AND Approved.LEAD_STAGE_ID=70 
    WHERE  CONVERT(DATE,dbo.LEAD_STAGE_LOG.CREATED_DATE) BETWEEN @From_Date and @To_Date --AND STATUS =11 
    AND CONVERT(DATE,Approved.CREATED_DATE) BETWEEN @From_Date and @To_Date
    GROUP BY SALES_DIVISION_ID,LEAD_POOL_DETAIL.ALLOCATED_TO

    UNION 

    SELECT SALES_DIVISION_ID,dbo.LEAD_POOL_DETAIL.ALLOCATED_TO, COUNT(DISTINCT dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID) AS No_Of_Approved
    FROM  dbo.LEAD_STAGE_LOG 
    INNER JOIN  dbo.LEAD_POOL_DETAIL ON dbo.LEAD_STAGE_LOG.LEAD_DETAIL_ID = dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID AND LEAD_STAGE_ID=60 
    LEFT OUTER JOIN dbo.LEAD_STAGE_LOG AS Lead_Complete ON Lead_Complete.LEAD_DETAIL_ID = LEAD_POOL_DETAIL.LEAD_DETAIL_ID  AND Lead_Complete.LEAD_STAGE_ID=70
    WHERE  CONVERT(DATE,dbo.LEAD_STAGE_LOG.CREATED_DATE) <@From_Date
    AND ( CONVERT(DATE,Lead_Complete.CREATED_DATE) BETWEEN @From_Date and @To_Date)
    GROUP BY SALES_DIVISION_ID,LEAD_POOL_DETAIL.ALLOCATED_TO

    )AS tbl_Intake ON dbo.USER_MASTER.USER_ID=tbl_Intake.ALLOCATED_TO AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID=tbl_Intake.SALES_DIVISION_ID AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID =@SALES_DIVISION_IDs

    LEFT OUTER JOIN 
    (
    SELECT DISTINCT dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID,ALLOCATED_TO,CONVERT(VARCHAR(10),dbo.FILE_MASTER.CLIENT_ID)+'-'+ISNULL(CONVERT(VARCHAR(11),dbo.FILE_MASTER.LOAN_SANCTION_DATE,110),'') AS No_of_ClientInward 
    ,( CASE 
    WHEN (ISNULL(dbo.FILE_MASTER.LOAN_AMOUNT,0)*100000)>=50000000 THEN 'A'
    WHEN (ISNULL(dbo.FILE_MASTER.LOAN_AMOUNT,0)*100000)<50000000 AND (ISNULL(dbo.FILE_MASTER.LOAN_AMOUNT,0)*100000)>=10000000 THEN 'B'
    WHEN (ISNULL(dbo.FILE_MASTER.LOAN_AMOUNT,0)*100000)<10000000 AND (ISNULL(dbo.FILE_MASTER.LOAN_AMOUNT,0)*100000)>=5000000 THEN 'C'
    WHEN (ISNULL(dbo.FILE_MASTER.LOAN_AMOUNT,0)*100000)<5000000 THEN 'D'
    ELSE 'Blank'
    END )AS Firm_Category ,dbo.FILE_MASTER.LOAN_AMOUNT
    FROM dbo.LEAD_POOL_DETAIL 
    INNER JOIN 
    (
    SELECT DISTINCT dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID
    FROM  dbo.LEAD_STAGE_LOG WITH(NOLOCK)
    INNER JOIN  dbo.LEAD_POOL_DETAIL WITH(NOLOCK)ON dbo.LEAD_STAGE_LOG.LEAD_DETAIL_ID = dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID AND LEAD_STAGE_ID=60 AND dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID >0
    LEFT OUTER JOIN  dbo.LEAD_STAGE_LOG AS Approved ON Approved.LEAD_DETAIL_ID = LEAD_POOL_DETAIL.LEAD_DETAIL_ID AND Approved.LEAD_STAGE_ID=70 
    WHERE  CONVERT(DATE,dbo.LEAD_STAGE_LOG.CREATED_DATE) BETWEEN @From_Date and @To_Date
    AND CONVERT(DATE,Approved.CREATED_DATE) BETWEEN @From_Date and @To_Date

    UNION

    SELECT DISTINCT dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID
    FROM  dbo.LEAD_STAGE_LOG 
    INNER JOIN  dbo.LEAD_POOL_DETAIL ON dbo.LEAD_STAGE_LOG.LEAD_DETAIL_ID = dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID AND LEAD_STAGE_ID=60 
    LEFT OUTER JOIN dbo.LEAD_STAGE_LOG AS Lead_Complete ON Lead_Complete.LEAD_DETAIL_ID = LEAD_POOL_DETAIL.LEAD_DETAIL_ID  AND Lead_Complete.LEAD_STAGE_ID=70
    WHERE  CONVERT(DATE,dbo.LEAD_STAGE_LOG.CREATED_DATE) <@From_Date
    AND ( CONVERT(DATE,Lead_Complete.CREATED_DATE) BETWEEN @From_Date and @To_Date)
    )AS tbl_Approved ON tbl_Approved.LEAD_DETAIL_ID = LEAD_POOL_DETAIL.LEAD_DETAIL_ID
    INNER JOIN dbo.FILE_MASTER ON dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID = dbo.FILE_MASTER.LEAD_DETAIL_ID  
    LEFT OUTER JOIN [dbo].[View_Clients_2014_Fresh_Inward] ON  dbo.FILE_MASTER.CLIENT_ID=[dbo].[View_Clients_2014_Fresh_Inward].CLIENT_ID AND  ISNULL(dbo.FILE_MASTER.LOAN_SANCTION_DATE,'') = ISNULL([dbo].[View_Clients_2014_Fresh_Inward].LOAN_SANCTION_DATE,'') 
    WHERE  CONVERT(DATE,dbo.View_Clients_2014_Fresh_Inward.INW_DATE) BETWEEN @From_Date and @To_Date 
    AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID =@SALES_DIVISION_IDs
    ) AS TBL_APP ON dbo.USER_MASTER.USER_ID = TBL_APP.ALLOCATED_TO AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID=TBL_APP.SALES_DIVISION_ID AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID =@SALES_DIVISION_IDs

    LEFT OUTER JOIN (SELECT dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID,ALLOCATED_TO,COUNT(DISTINCT dbo.FILE_MASTER.FILE_ID) AS No_of_FilesInward FROM dbo.LEAD_POOL_DETAIL WITH(NOLOCK)INNER JOIN dbo.FILE_MASTER WITH(NOLOCK)ON dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID = dbo.FILE_MASTER.LEAD_DETAIL_ID AND STATUS=6 WHERE  CONVERT(DATE,INWARD_DATE) BETWEEN @From_Date and @To_Date AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID =@SALES_DIVISION_IDs GROUP BY dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID,ALLOCATED_TO) AS TBL_Inward ON dbo.USER_MASTER.USER_ID = TBL_Inward.ALLOCATED_TO AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID=TBL_Inward.SALES_DIVISION_ID 

    LEFT OUTER JOIN dbo.USER_MASTER AS UMTM ON UMTM.USER_ID= UM_TM_ID 
    LEFT OUTER JOIN dbo.SALES_DIVISION_USER_MAPPER AS SM ON dbo.MARKETING_PERSON_MASTER.SALES_DIVISION_ID = SM.DIVISION_ID AND SM.ROLE_ID=2  AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID =@SALES_DIVISION_IDs
    LEFT OUTER JOIN dbo.USER_MASTER AS USER_SM ON USER_SM.USER_ID= SM.USER_ID 
    LEFT OUTER JOIN (
    SELECT SALES_DIVISION_ID,dbo.LEAD_POOL_DETAIL.ALLOCATED_TO,COUNT(DISTINCT dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID) AS CF_Intake 
    FROM  dbo.LEAD_STAGE_LOG 
    INNER JOIN  dbo.LEAD_POOL_DETAIL ON dbo.LEAD_STAGE_LOG.LEAD_DETAIL_ID = dbo.LEAD_POOL_DETAIL.LEAD_DETAIL_ID AND LEAD_STAGE_ID=60 
    LEFT OUTER JOIN dbo.LEAD_STAGE_LOG AS Lead_Complete ON Lead_Complete.LEAD_DETAIL_ID = LEAD_POOL_DETAIL.LEAD_DETAIL_ID  AND Lead_Complete.LEAD_STAGE_ID=70
    LEFT OUTER JOIN dbo.LEAD_STAGE_LOG AS Lead_Rejected ON Lead_Rejected.LEAD_DETAIL_ID = LEAD_POOL_DETAIL.LEAD_DETAIL_ID  AND Lead_Rejected.LEAD_STAGE_ID=80
    WHERE  CONVERT(DATE,dbo.LEAD_STAGE_LOG.CREATED_DATE) <@From_Date 
    AND ( CONVERT(DATE,Lead_Complete.CREATED_DATE)>=@From_Date  OR Lead_Complete.CREATED_DATE IS NULL)
    AND ( CONVERT(DATE,Lead_Rejected.CREATED_DATE)>=@From_Date  OR Lead_Rejected.CREATED_DATE IS NULL)
    GROUP BY dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID,dbo.LEAD_POOL_DETAIL.ALLOCATED_TO )AS CF_Intake ON CF_Intake.ALLOCATED_TO = USER_MASTER.USER_ID AND CF_Intake.SALES_DIVISION_ID = LEAD_POOL_DETAIL.SALES_DIVISION_ID AND dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID =@SALES_DIVISION_IDs

    WHERE  0=0 
    GROUP BY ISNULL(UMTM.USER_ID,USER_SM.USER_ID),ISNULL(UMTM.USER_NAME,USER_SM.USER_NAME),dbo.LEAD_POOL_DETAIL.SALES_DIVISION_ID,dbo.USER_MASTER.USER_ID,dbo.USER_MASTER.USER_NAME,dbo.SALES_FOLLOWUP_TRANSCTION.CREATED_BY,TBL_APP.Firm_Category,TBL_APP.No_of_ClientInward,ISNULL(TBL_APP.LOAN_AMOUNT,0),dbo.USER_MASTER.STATUS,tbl_Attendance.Mkt_Attendance,No_of_FilesInward,CF_Intake.CF_Intake


    Please note a statistics for query ..

    Table 'LEAD_STAGE_LOG'. Scan count 99, logical reads 11211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'LEAD_POOL_DETAIL'. Scan count 45, logical reads 10890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 172, logical reads 88092, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'USER_MASTER'. Scan count 18, logical reads 108480, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SALES_DIVISION_USER_MAPPER'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FILE_MASTER'. Scan count 27, logical reads 2824, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Employees'. Scan count 9, logical reads 178, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'DeviceLogs'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'LEAD_POOL_TRANSACTION'. Scan count 9, logical reads 739, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SALES_FOLLOWUP_TRANSCTION'. Scan count 9, logical reads 4631, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'MARKETING_PERSON_MASTER'. Scan count 9, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'EMPLOYEE_MASTER'. Scan count 9, logical reads 169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Warning: Null value is eliminated by an aggregate or other SET operation.

    * Query taking 42 sec. to return 298 rows ..

    As all the sub queries returning a minimum rows so , don't have an extra index for the tables columns which are includes in where clauses , group by except that an clustered index per table according to primary key is there in all the tables.

    for eg. i would say 

    table dbo.LEAD_POOL_DETAIL have 40915 records ..

    table dbo.LEAD_POOL_TRANSACTION have 40256 records ..

    table dbo.SALES_FOLLOWUP_TRANSCTION have 137143 records ..

    table dbo.LEAD_STAGE_LOG have 81000 records ..

    only the table involved in [View_Marketing_Person_Attendance]  is DeviceLog has more than 5 lacs of rows and we have an index on date filter

    important thing about main query ..

    sub query tbl_Attendance returns 249 rows in 0 sec.

     tbl_Intake returns 76 rows in 0 sec.

    TBL_APP  returns 262 rows in 0 sec.

    TBL_Inward returns 26 rows in 0 sec.

    CF_Intake returns 36 rows in 0 sec.

    as the plan for main query is large so , cant paste here ..

    please give me suggestions on the basis of above information or can ask for additional thing if required ..

    Many thanks for your support ..



    Dilip Patil..

    Monday, July 6, 2015 5:35 AM
  • Hi Dilip,

    Thanks for posting more information. I think there are a few things to consider:

    • The query plan shows both a sort and hash operator. Both of these can create objects in tempdb (work tables / files)
    • You have quite a bit of scans and reads. I don't see that shown in the graphical plan you posted but it also looks like that plan is not the whole story. I would look over the query and eliminate the converts. They are very bad for performance and you should be storing the data in the way that you plan to pull it out. Lines like "WHERE CONVERT(DATE,ATTENDANCE_DATE) BETWEEN @From_Date and @To_Date GROUP BY EmployeeCode )AS tbl_Attendance  ON CONVERT(INT,tbl_Attendance.EmployeeCode)=EMPLOYEE_CODE" are very bad. Never convert columns that are used in the predicate of a WHERE or JOIN. This can force SQL Server to scan the data instead of seeking it and in turn wreck performance.

    Clean up these things and the query should run much better.

    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. :)

    Monday, July 6, 2015 5:53 AM
  • Daniel .. actually the sub query which is running on the view , the view is designed to fetched attendance data from another database maintained by ESSL pvvt ltd. and in that the employee table has a column EmployeeCode which is nvarchar(100) and in our database it is int column storing same integer numbers like in that db so , i am converting essl employe_code to int  (as this not have any index on it so, not an issue of cardinallity estimates i guess. )

    hope you understand my action .. Please , guide instead of converting but i cant change that datatype from nvarchar to int .(I dont understand why those peoples storing integer numbers in nvarchar(100) column )


    Dilip Patil..

    Monday, July 6, 2015 6:10 AM
  • Dilip, can you update us on this?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, December 15, 2016 2:45 AM