Can the hash matches in this plan be removed – best way to optimize the query ?
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
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
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.
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.
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.