Can the hash matches in this plan be removed – best way to optimize the query ?

Pearljammer1 2014-11-25 12:17:05

SELECT
dist.[DESCRIPTION] AS [District]
,tg.[DESCRIPTION] AS [Timesheet Group]
,tl.TIMESHEET_LINE_REF
,tw.WEEK_ENDING
,emp.EMPLOY_REF
,emp.FORENAME, emp.SURNAME
,shop.BRANCH AS [Shop]
,shop.LOC_REF AS [LBO Shop Code]
,COALESCE(tit.CODE, 0) AS [CODE]
,COALESCE(ab.DESCRIPTION, tit.DESCRIPTION) AS DESCRIPTION
,CASE WHEN COALESCE(il.DESCRIPTION, ") = " THEN " ELSE il.DESCRIPTION END AS Reason
,tl.MONETARY_VALUE
,tl.MONDAY_HOURS
,tl.TUESDAY_HOURS
,tl.WEDNESDAY_HOURS
,tl.THURSDAY_HOURS
,tl.FRIDAY_HOURS
,tl.SATURDAY_HOURS
,tl.SUNDAY_HOURS
,tl.WEEK_HOURS
,case when patindex('%||%', th.notes) > 0 then ltrim(rtrim((select substring(th.notes, 0 + 4, charindex('||', right(th.notes, (len(th.notes) – 4))))))) else " end AS 'Standard Notes'
,case when patindex('%||%', th.notes) > 0 then ltrim(rtrim((select substring(th.notes, charindex('||', right(th.notes, (len(th.notes) – 2))) + 5, 1000)))) else th.notes end AS 'Additional Notes'
FROM
dbo.TIMESHEET_LINE tl
INNER JOIN dbo.TIMESHEET_HEADER th ON tl.TIMESHEET_HEADER_REF = th.TIMESHEET_HEADER_REF
INNER JOIN dbo.EMPLOYEE_table emp ON th.EMPLOY_REF = emp.EMPLOY_REF
INNER JOIN dbo.TIMESHEET_WEEK tw ON tw.TIMESHEET_WEEK_REF = th.TIMESHEET_WEEK_REF
LEFT JOIN dbo.timesheet_group_input_type tgit ON tl.TIMESHEET_GROUP_INPUT_TYPE_REF = tgit.TIMESHEET_GROUP_INPUT_TYPE_REF
LEFT JOIN dbo.timesheet_input_type tit ON tgit.TIMESHEET_INPUT_TYPE_REF = tit.TIMESHEET_INPUT_TYPE_REF
LEFT JOIN dbo.TIMESHEET_GROUP tg ON th.TIMESHEET_GROUP_REF = tg.TIMESHEET_GROUP_REF
LEFT JOIN [dbo].[TIMESHEET_ABSENCE] ta ON ta.TIMESHEET_ABSENCE_REF = tl.TIMESHEET_ABSENCE_REF
LEFT JOIN dbo.V_ILLNES il ON il.CODE = ta.ABSENCE_REASON
LEFT JOIN dbo.V_ABS ab ON ab.CODE = ta.ABSENCE_TYPE
LEFT JOIN dbo.V_DEPT dist ON emp.DEPARTMENT = dist.CODE
LEFT JOIN dbo.LOCATION shop ON emp.LOCATION = shop.LOC_REF

/storage/attachments/1072-tmpBC1C.queryanalysis
Pearljammer1 2014-11-25 12:58:25
that was the actual plan – I ran it is ssms then used the View with sequel sentry plan explorer option.
SQLkiwi 2014-11-25 13:06:16
Oh right, sorry I was still looking at the plan from your other question! How long does this take to return results? What is your performance objective? Running directly from PE would still be beneficial – it would capture run time and reads, for example.
Jack 2014-12-24 16:10:43
You could try re-writing the query to use some INNER JOIN's instead of OUTER JOIN's like this:

SELECT
    dist.[DESCRIPTION] AS [District],
    tg.[DESCRIPTION] AS [Timesheet Group],
    tl.TIMESHEET_LINE_REF,
    tw.WEEK_ENDING,
    emp.EMPLOY_REF,
    emp.FORENAME,
    emp.SURNAME,
    shop.BRANCH AS [Shop],
    shop.LOC_REF AS [LBO Shop Code],
    COALESCE(tit.CODE, 0) AS [CODE],
    COALESCE(ab.DESCRIPTION, tit.DESCRIPTION) AS DESCRIPTION,
    CASE WHEN COALESCE(il.DESCRIPTION, '') = '' THEN ''
         ELSE il.DESCRIPTION
    END AS Reason,
    tl.MONETARY_VALUE,
    tl.MONDAY_HOURS,
    tl.TUESDAY_HOURS,
    tl.WEDNESDAY_HOURS,
    tl.THURSDAY_HOURS,
    tl.FRIDAY_HOURS,
    tl.SATURDAY_HOURS,
    tl.SUNDAY_HOURS,
    tl.WEEK_HOURS,
    CASE WHEN PATINDEX('%||%', th.notes) > 0 THEN LTRIM(RTRIM((
                                                               SELECT
                                                                SUBSTRING(th.notes, 0 + 4, CHARINDEX('||', RIGHT(th.notes, (LEN(th.notes) - 4))))
                                                              )))
         ELSE ''
    END AS 'Standard Notes',
    CASE WHEN PATINDEX('%||%', th.notes) > 0 THEN LTRIM(RTRIM((
                                                               SELECT
                                                                SUBSTRING(th.notes, CHARINDEX('||', RIGHT(th.notes, (LEN(th.notes) - 2))) + 5, 1000)
                                                              )))
         ELSE th.notes
    END AS 'Additional Notes'
FROM
    dbo.TIMESHEET_LINE tl
    INNER JOIN dbo.TIMESHEET_HEADER th
        ON tl.TIMESHEET_HEADER_REF = th.TIMESHEET_HEADER_REF
    INNER JOIN dbo.EMPLOYEE_table emp
        ON th.EMPLOY_REF = emp.EMPLOY_REF
    INNER JOIN dbo.TIMESHEET_WEEK tw
        ON tw.TIMESHEET_WEEK_REF = th.TIMESHEET_WEEK_REF
    LEFT JOIN dbo.timesheet_group_input_type tgit
    INNER JOIN dbo.timesheet_input_type tit
        ON tgit.TIMESHEET_INPUT_TYPE_REF = tit.TIMESHEET_INPUT_TYPE_REF
        ON tl.TIMESHEET_GROUP_INPUT_TYPE_REF = tgit.TIMESHEET_GROUP_INPUT_TYPE_REF
    LEFT JOIN dbo.TIMESHEET_GROUP tg
        ON th.TIMESHEET_GROUP_REF = tg.TIMESHEET_GROUP_REF
    LEFT JOIN [dbo].[TIMESHEET_ABSENCE] ta
    INNER JOIN dbo.V_ILLNES il
        ON il.CODE = ta.ABSENCE_REASON
    INNER JOIN dbo.V_ABS ab
        ON ab.CODE = ta.ABSENCE_TYPE
        ON ta.TIMESHEET_ABSENCE_REF = tl.TIMESHEET_ABSENCE_REF
    LEFT JOIN dbo.V_DEPT dist
        ON emp.DEPARTMENT = dist.CODE
    LEFT JOIN dbo.LOCATION shop
        ON emp.LOCATION = shop.LOC_REF

I think logically this is the same query, if you expect there to always be matching rows between time_sheet_grup_input_type and time_sheet_input_type and between time_sheet_absence and b_illnes and v_abs.

The only other thing I could suggest would be to include some type of filtering. This code returns all rows for all time and I'd think you'd want to reduce the rows returned to a specific time frame.

November 25, 2014
No Comments