November 14, 2014 at 6:53 am
Hi
This code has been written by a developer and it seems to be causing an issue performance wise.
It is taking over 2 minutes to run in SSMS - and when run from the app it times out.
We cannot use temp tables either because the 3rd party vendor doesn't allow it for some reason.
Looking at the execution plan I see that an Index seek with estimated number of rows = 1302 and Actual number of rows = 6066879, yet when I look on the table in question all the statistics where updated last night. So why the huge discrepancy in the actual and estimated ?
here is the query:
ET DATEFORMAT DMY;
WITH WP AS
(
SELECT
EWP.EMPLOY_REF
,WP.PATTNDATE
,WP.[HOURS] AS [HOURS]
FROM
[dbo].[WORKPATN] WP
INNER JOIN dbo.EMPLOYEE_WORKPATTERN EWP ON EWP.WK_PATTN = WP.WORKPATTERN AND GETDATE() BETWEEN EWP.WORKPATTERN_START AND EWP.WORKPATTERN_END
)
,WPO AS
(
SELECT
WEEK_ENDING
,EMPLOY_REF
,SUM(MO) AS MO
,SUM(TU) AS TU
,SUM(WE) AS WE
,SUM(TH) AS TH
,SUM(FR) AS FR
,SUM(SA) AS SA
,SUM(SU) AS SU
FROM
(
SELECT DISTINCT
CAST(TW.WEEK_ENDING AS [DATE]) AS WEEK_ENDING
,TH.EMPLOY_REF
,CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING -6, WP.PATTNDATE -6) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS MO
,CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING -5, WP.PATTNDATE -5) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS TU
,CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING -4, WP.PATTNDATE -4) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS WE
,CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING -3, WP.PATTNDATE -3) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS TH
,CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING -2, WP.PATTNDATE -2) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS FR
,CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING -1, WP.PATTNDATE -1) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS SA
,CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING, WP.PATTNDATE) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS SU
FROM
dbo.TIMESHEET_HEADER TH
INNER JOIN dbo.TIMESHEET_WEEK TW ON TW.TIMESHEET_WEEK_REF = TH.TIMESHEET_WEEK_REF
LEFT JOIN dbo.WORKPATTERN_OVERRIDE WO ON WO.EMPLOY_REF = TH.EMPLOY_REF
LEFT JOIN WP ON WP.EMPLOY_REF = WO.EMPLOY_REF
GROUP BY TW.WEEK_ENDING,TH.EMPLOY_REF,WO.[DATE],WO.[HOURS],WP.PATTNDATE,WP.[HOURS]
)AS T
GROUP BY T.WEEK_ENDING, T.EMPLOY_REF
)
SELECT
T.District
,T.Shop
,T.[LBO Shop Code]
,T.[Timesheet Group]
,T.[Week Ending]
,T.EMPLOY_REF
,T.[Employee Name]
,SUM(T.[Monday Hours]) AS [Monday Hours]
,SUM(T.[Tuesday Hours]) AS [Tuesday Hours]
,SUM(T.[Wednesday Hours]) AS [Wednesday Hours]
,SUM(T.[Thursday Hours]) AS [Thursday Hours]
,SUM(T.[Friday Hours]) AS [Friday Hours]
,SUM(T.[Saturday Hours]) AS [Saturday Hours]
,SUM(T.[Sunday Hours]) AS [Sunday Hours]
,SUM(T.[Week Hours]) AS [Week Hours]
,SUM(T.[Days Taken]) AS [Days Taken]
FROM
(
SELECT
dist.[DESCRIPTION] AS [District]
,shop.BRANCH AS [Shop]
,shop.LOC_REF AS [LBO Shop Code]
,tg.[DESCRIPTION] AS [Timesheet Group]
,tw.WEEK_ENDING AS [Week Ending]
,emp.EMPLOY_REF
,COALESCE(emp.PREFERRED_NAME,emp.FORENAME) + ' ' + emp.SURNAME AS [Employee Name]
,SUM(tl.MONDAY_HOURS) AS [Monday Hours]
,SUM(tl.TUESDAY_HOURS) AS [Tuesday Hours]
,SUM(tl.WEDNESDAY_HOURS) AS [Wednesday Hours]
,SUM(tl.THURSDAY_HOURS) AS [Thursday Hours]
,SUM(tl.FRIDAY_HOURS) AS [Friday Hours]
,SUM(tl.SATURDAY_HOURS) AS [Saturday Hours]
,SUM(tl.SUNDAY_HOURS) AS [Sunday Hours]
,SUM(tl.WEEK_HOURS) AS [Week Hours]
,CAST(
CASE WHEN tl.MONDAY_HOURS <> 0 AND W.MO <> 0 THEN tl.MONDAY_HOURS / W.MO ELSE 0 END
+ CASE WHEN tl.TUESDAY_HOURS <> 0 AND W.TU <> 0 THEN tl.TUESDAY_HOURS / W.TU ELSE 0 END
+ CASE WHEN tl.WEDNESDAY_HOURS <> 0 AND W.WE <> 0 THEN tl.WEDNESDAY_HOURS / W.WE ELSE 0 END
+ CASE WHEN tl.THURSDAY_HOURS <> 0 AND W.TH <> 0 THEN tl.THURSDAY_HOURS / W.TH ELSE 0 END
+ CASE WHEN tl.FRIDAY_HOURS <> 0 AND W.FR <> 0 THEN tl.FRIDAY_HOURS / W.FR ELSE 0 END
+ CASE WHEN tl.SATURDAY_HOURS <> 0 AND W.SA <> 0 THEN tl.SATURDAY_HOURS / W.SA ELSE 0 END
+ CASE WHEN tl.SUNDAY_HOURS <> 0 AND W.SU <> 0 THEN tl.SUNDAY_HOURS / W.SU ELSE 0 END
AS decimal(5,2))
AS [Days Taken]
FROM
dbo.TIMESHEET_LINE tl
INNER JOIN dbo.TIMESHEET_HEADER th ON tl.timesheet_header_ref = th.timesheet_header_ref
LEFT JOIN dbo.TIMESHEET_ABSENCE ta ON tl.TIMESHEET_HEADER_REF = ta.TIMESHEET_HEADER_REF AND tl.TIMESHEET_ABSENCE_REF = ta.TIMESHEET_ABSENCE_REF
LEFT JOIN dbo.EMPLOYEE_TABLE emp ON th.EMPLOY_REF = emp.EMPLOY_REF
LEFT JOIN dbo.TIMESHEET_GROUP tg ON th.TIMESHEET_GROUP_REF = tg.TIMESHEET_GROUP_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 tgit.timesheet_group_input_type_ref = tl.timesheet_group_input_type_ref
LEFT JOIN dbo.V_DEPT dist ON emp.DEPARTMENT = dist.CODE
LEFT JOIN dbo.LOCATION shop ON emp.LOCATION = shop.LOC_REF
INNER JOIN WPO W ON W.EMPLOY_REF = th.EMPLOY_REF AND tw.WEEK_ENDING = W.WEEK_ENDING
WHERE
tl.TIMESHEET_GROUP_INPUT_TYPE_REF = 0
AND
ta.ABSENCE_TYPE = 'HOLIDAY'
GROUP BY
dist.[DESCRIPTION]
,shop.BRANCH
,shop.LOC_REF
,tg.[DESCRIPTION]
,tw.WEEK_ENDING
,emp.EMPLOY_REF
,COALESCE(emp.PREFERRED_NAME,emp.FORENAME) + ' ' + emp.SURNAME
,tl.MONDAY_HOURS
,tl.TUESDAY_HOURS
,tl.WEDNESDAY_HOURS
,tl.THURSDAY_HOURS
,tl.FRIDAY_HOURS
,tl.SATURDAY_HOURS
,tl.SUNDAY_HOURS
,W.MO
,W.TU
,W.WE
,W.TH
,W.FR
,W.SA
,W.SU
) AS T
GROUP BY
T.District,
T.Shop,
T.[LBO Shop Code],
T.[Timesheet Group],
T.[Week Ending],
T.EMPLOY_REF,
T.[Employee Name]
order by T.EMPLOY_REF
November 14, 2014 at 7:00 am
Table definitions, index definitions and execution plan please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2014 at 7:25 am
Complex queries run the risk of failing to obtain a satisfactory execution plan, as the optimiser doesn't have sufficient time to explore all possibilities. You'll get a plan so the query will run but it won't be a good one. The property sheet of the leftmost
operator in the execution plan will tell you if this is the case. IMHO it should be a warning, it's too significant to be hidden away.
The more tables you have in your query, the more possibilities there are for joining them. Your query references at least 14. Any query referencing more than 7 or 8 tables should be checked for optimiser timeout.
It's a pity you can't use #temp tables, that's a helluva limitation. It would take minutes to make this query work properly. Having said that, it would benefit from a revisit anyway. Why is this table included?
LEFT JOIN dbo.TIMESHEET_GROUP_INPUT_TYPE tgit ON tgit.timesheet_group_input_type_ref = tl.timesheet_group_input_type_ref.
CTE's have encouraged lazy coding practice. You're likely to find other quick wins.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 14, 2014 at 8:29 am
Not sure if i have done correctly - attempting to attach the execution plan !!:-)
A
November 14, 2014 at 8:44 am
@Gail - Ive added the execution plan just now.
the table is defined as such :
USE [databaseX]
GO
/****** Object: Table [dbo].[WORKPATN] Script Date: 14/11/2014 15:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[WORKPATN](
[WORKPATN_REF] [INT] IDENTITY(1,1) NOT NULL,
[WORKPATTERN] [VARCHAR](10) NOT NULL,
[HOURS] [DECIMAL](19, 4) NULL,
[PATTNDATE] [DATETIME] NULL,
[OT_RATE] [DECIMAL](19, 4) NULL,
[BANKHOLIDAY] [BIT] NOT NULL,
CONSTRAINT [PK_WORKPATN] PRIMARY KEY CLUSTERED
(
[WORKPATN_REF] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[WORKPATN] ADD CONSTRAINT [DF_WORKPATN_HOURS] DEFAULT (0.0) FOR [HOURS]
GO
ALTER TABLE [dbo].[WORKPATN] ADD CONSTRAINT [DF_WORKPATN_OT_RATE] DEFAULT (0.0) FOR [OT_RATE]
GO
ALTER TABLE [dbo].[WORKPATN] ADD DEFAULT (0) FOR [BANKHOLIDAY]
GO
The indexes are scripted as such:
USE [Databasex]
GO
/****** Object: Index [WORKPATTERN_DATE] Script Date: 14/11/2014 15:40:24 ******/
CREATE UNIQUE NONCLUSTERED INDEX [WORKPATTERN_DATE] ON [dbo].[WORKPATN]
(
[WORKPATTERN] ASC,
[PATTNDATE] ASC
)
INCLUDE ( [HOURS],
[OT_RATE],
[BANKHOLIDAY]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO
USE [databasex]
GO
/****** Object: Index [WORKPATTERN] Script Date: 14/11/2014 15:40:17 ******/
CREATE NONCLUSTERED INDEX [WORKPATTERN] ON [dbo].[WORKPATN]
(
[WORKPATTERN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO
USE [databasex]
GO
/****** Object: Index [PK_WORKPATN] Script Date: 14/11/2014 15:40:09 ******/
ALTER TABLE [dbo].[WORKPATN] ADD CONSTRAINT [PK_WORKPATN] PRIMARY KEY CLUSTERED
(
[WORKPATN_REF] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO
USE [databasex]
GO
/****** Object: Index [PATTNDATE] Script Date: 14/11/2014 15:39:52 ******/
CREATE NONCLUSTERED INDEX [PATTNDATE] ON [dbo].[WORKPATN]
(
[PATTNDATE] ASC
)
INCLUDE ( [WORKPATTERN],
[HOURS]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO
November 14, 2014 at 8:53 am
PearlJammer1 (11/14/2014)
We cannot use temp tables either because the 3rd party vendor doesn't allow it for some reason.
Heh... I'm always amazed but such things. What they don't realize is that purposefully not using Temp Tables can cause system spawned Temp Tables to just explode. It's frequently much better to use "Divide'n'Conquer" methods to build smaller Temp Tables and join to those than to let the system build massive work tables to support such things has Hash Joins or trying to resolve accidental Many-to-Many joins that require a UNIQUE or GROUP BY somewhere in the code to prevent duplicated rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2014 at 5:48 pm
The query plan is timing out, and I'm not surprised!
Take this one simple looking expression in the WPO CTE:
CASE WHEN WO.[DATE] = COALESCE(TW.WEEK_ENDING -6, WP.PATTNDATE -6) THEN COALESCE(WO.[HOURS], WP.[HOURS]) ELSE 0.0 END AS MO
It translates as this - see comments inside for immediately obvious areas to investigate
[Expr1033] = Scalar Operator(
case
--== WO.DATE can be NULL - if the data doesn't allow for NULLS,
--== change the join to an INNER JOIN
WHEN [SnowdropKCSHR2011_Train].[dbo].[WORKPATTERN_OVERRIDE].[DATE] as [WO].[DATE]=
CASE
--== TIMESHEET_WEEK is on an INNER JOIN,
--== so WEEK_ENDING probably is never NULL
--== unless you have NULLS in that column,
--== so this logic may be redundant
WHEN ([SnowdropKCSHR2011_Train].[dbo].[TIMESHEET_WEEK].[WEEK_ENDING] as [TW].[WEEK_ENDING]-'1900-01-07 00:00:00.000') IS NOT NULL
THEN [SnowdropKCSHR2011_Train].[dbo].[TIMESHEET_WEEK].[WEEK_ENDING] as [TW].[WEEK_ENDING]-'1900-01-07 00:00:00.000'
ELSE [Expr1030]-'1900-01-07 00:00:00.000'
END
THEN
--== BOTH WORKPATTERN_OVERRIDE and "WP" are OUTER JOINS,
--== yet there is no allowance for this,
--== so you could still return a NULL for any day
CASE
WHEN [SnowdropKCSHR2011_Train].[dbo].[WORKPATTERN_OVERRIDE].[HOURS] as [WO].[HOURS] IS NOT NULL
THEN CONVERT_IMPLICIT(decimal(19,4),[SnowdropKCSHR2011_Train].[dbo].[WORKPATTERN_OVERRIDE].[HOURS] as [WO].[HOURS],0)
ELSE [Expr1031]
END
ELSE (0.0000)
END),
Now, whether this whole query is even correct comes into question.
It needs a code review - think about what you are trying to achieve in terms of sets,
Maybe draw a quick VENN diagram to help visualise the relationships and intersections required
This can help to understand the right way to join the tables.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply