TSQL Performance Optimization GURUS Activate
-
OK TSQL Performance GURUS...
I need help understanding why splitting this query up performs in 8 seconds and together it performs in 40+ minutes.
What strategies can I use to modify either or both to perform better?
I used information from a topic entitled "Finding datetime range intersections and durations" to assist in getting start and end date ranges and durations to display.
I received help in topic entitled "Find Datetime Range Durations" to extend this to show a case where there is a start date NOT paired up with an end date (e.g. employee is still active).
In this case I'd like the date the query is run or parameterized date to be used as the final end date in order to calculate duration.
Current Result: (assuming query run on 2005-08-09):
StartTime EndTime AddressNumber Dur
1992-06-09 00:00:00.000 1997-12-05 00:00:00.000 1169 2005
2004-08-24 00:00:00.000 2004-11-26 00:00:00.000 1169 94
2005-07-21 00:00:00.000 2005-08-09 00:00:00.000 1169 19
Long Query SQL:
set statistics profile on
set statistics io on
set statistics time on
GO
-- Value is 1 for start datetime and 0 for end datetime
DECLARE @T2 table (StartTime datetime,EndTime datetime,AddressNumber float,Dur real)
Declare @LimitDate datetime
set @LimitDate = '20050809'
INSERT INTO @T2
SELECT t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.AddressNumber,Datediff(dd,t3.Datetime,t3.NextDatetime) AS 'Dur'
FROM (
SELECT t1.AddressNumber , t1.HistoryDate , t1.DateType , isnull(MIN( t2.HistoryDate ),@LimitDate)
FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1
LEFT JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2
ON t1.AddressNumber = t2.AddressNumber and t2.HistoryDate > t1.HistoryDate
WHERE (t2.DateType = 0 or t2.HistoryDate is null) and t1.HistoryDate <= @LimitDate
GROUP BY t1.AddressNumber , t1.HistoryDate , t1.DateType
) AS t3 ( AddressNumber , Datetime , value , NextDateTime )
WHERE t3.value = 1
GROUP BY t3.AddressNumber, t3.Value, t3.Datetime, t3.NextDatetime
ORDER BY t3.Datetime ASC
SELECT * From @T2 order By AddressNumber, StartTime
Broken Up Query SQL:
set statistics profile on
set statistics io on
set statistics time on
GO
-- Value is 1 for start datetime and 0 for end datetime
DECLARE @T2 table (StartTime datetime,EndTime datetime,AddressNumber float,Dur real)
Declare @LimitDate datetime
set @LimitDate = '20050809'
SELECT t1.AddressNumber , t1.HistoryDate as [DateTime] , t1.DateType as value , isnull(MIN( t2.HistoryDate ),@LimitDate) as NextDateTime
into #T3
FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1
LEFT JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2
ON t1.AddressNumber = t2.AddressNumber and t2.HistoryDate > t1.HistoryDate
WHERE (t2.DateType = 0 or t2.HistoryDate is null) and t1.HistoryDate <= @LimitDate
GROUP BY t1.AddressNumber , t1.HistoryDate , t1.DateType
SELECT t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.AddressNumber,Datediff(dd,t3.Datetime,t3.NextDatetime) AS 'Dur'
FROM #T3 t3
WHERE t3.value = 1
GROUP BY t3.AddressNumber, t3.Value, t3.Datetime, t3.NextDatetime
ORDER BY t3.Datetime ASC
HERE ARE THE STATS and EXECUTION PLAN RESULTS:
Long Query:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 5, logical reads 249, physical reads 0, read-ahead reads 0.
Table '#2ACAAC4E'. Scan count 0, logical reads 607, physical reads 0, read-ahead reads 0.
Table 'F060116'. Scan count 2927937, logical reads 6506118, physical reads 0, read-ahead reads 0.
Table 'F08042'. Scan count 659, logical reads 7247023, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 23 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 27 ms.
Table '#2ACAAC4E'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 9 ms.
Records Affected : 606
Records Affected : 629
Records Affected : 629
Broken Up Query:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 265 ms, elapsed time = 267 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#T3___00000008E03D'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'F060116'. Scan count 8886, logical reads 19762, physical reads 0, read-ahead reads 0.
Table 'F08042'. Scan count 2, logical reads 21994, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 30 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 33 ms.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 34 ms.
Table '#T3__00000008E03D'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 9 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 12 ms.
Records Affected : 676
Records Affected : 697
Records Affected : 697
Long Query Execution Plan:
_x0023_ Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions 1 606 1 INSERT INTO @T2SELECT t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.AddressNumber,Datediff(dd,t3.Datetime,t3.NextDatetime) AS 'Dur'FROM ( SELECT t1.AddressNumber , t1.HistoryDate , t1.DateType , isnull(MIN( t2.HistoryDate ),@LimitDate) FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1 LEFT JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2 ON t1.AddressNumber = t2.AddressNumber and t2.HistoryDate > t1.HistoryDate WHERE (t2.DateType = 0 or t2.HistoryDate is null) and t1.HistoryDate <= @LimitDate GROUP BY t1.AddressNumber , t1.HistoryDate , t1.DateType ) AS t3 ( AddressNumber , Datetime , value , NextDateTime ) WHERE t3.value = 1GROUP BY t3.AddressNumber, t3.Value, t3.Datetime, t3.NextDatetimeORDER BY t3.Datetime ASC 1 1 0 ? ? ? ? 10.352766 ? ? ? 19.5778828 ? ? INSERT false ? 2 606 1 |--Table Insert(OBJECT 
@T2), SET@T2.[Dur]=[Expr1016], @T2.[AddressNumber]=[F08042].[JWAN8], @T2.[EndTime]=[Expr1015], @T2.[StartTime]=[Expr1005]))1 2 1 Table Insert Insert OBJECT @T2), SET@T2.[Dur]=[Expr1016], @T2.[AddressNumber]=[F08042].[JWAN8], @T2.[EndTime]=[Expr1015], @T2.[StartTime]=[Expr1005])? 10.352766 0.0167567562 1.03527664E-05 15 19.5778828 ? ? PLAN_ROW false 1 3 606 1 |--Top(ROWCOUNT est 0) 1 3 2 Top Top ? ? 10.352766 0 1.03527668E-06 35 19.5611153 [F08042].[JWAN8], [Expr1005], [Expr1015], [Expr1016] ? PLAN_ROW false 1 4 606 1 |--Compute Scalar(DEFINE [Expr1016]=Convert(datediff(day, [Expr1005], [Expr1015]))))1 4 3 Compute Scalar Compute Scalar DEFINE [Expr1016]=Convert(datediff(day, [Expr1005], [Expr1015])))[Expr1016]=Convert(datediff(day, [Expr1005], [Expr1015])) 10.352766 0 1.03527668E-06 35 19.5611153 [F08042].[JWAN8], [Expr1005], [Expr1015], [Expr1016] ? PLAN_ROW false 1 5 606 1 |--Compute Scalar(DEFINE [Expr1015]=isnull([Expr1014], [@LimitDate])))1 5 4 Compute Scalar Compute Scalar DEFINE [Expr1015]=isnull([Expr1014], [@LimitDate]))[Expr1015]=isnull([Expr1014], [@LimitDate]) 10.352766 0 1.03527668E-06 31 19.5611134 [F08042].[JWAN8], [Expr1005], [Expr1015] ? PLAN_ROW false 1 6 606 1 |--Stream Aggregate(GROUP BY [Expr1005], [F08042].[JWAN8]) DEFINE[Expr1014]=MIN([Expr1012])))1 6 5 Stream Aggregate Aggregate GROUP BY [Expr1005], [F08042].[JWAN8])[Expr1014]=MIN([Expr1012]) 10.352766 0 9.063443E-05 31 19.5611134 [F08042].[JWAN8], [Expr1005], [Expr1014] ? PLAN_ROW false 1 7 665 1 |--Sort(ORDER BY [Expr1005] ASC, [F08042].[JWAN8] ASC))1 7 6 Sort Sort ORDER BY [Expr1005] ASC, [F08042].[JWAN8] ASC)? 15.1764507 0.0112612611 0.000192927109 31 19.5610218 [F08042].[JWAN8], [Expr1005], [Expr1012] ? PLAN_ROW false 1 8 665 1 |--Filter(WHERE [Expr1011]=0 OR [Expr1012]=NULL))1 8 7 Filter Filter WHERE [Expr1011]=0 OR [Expr1012]=NULL)? 15.1764507 0 1.37930056E-05 35 19.5495682 [F08042].[JWAN8], [Expr1005], [Expr1012] ? PLAN_ROW false 1 9 904 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES [F08042].[JWAN8], [Expr1005]))1 9 8 Nested Loops Left Outer Join OUTER REFERENCES [F08042].[JWAN8], [Expr1005])? 15.6738691 0 0.000184010321 35 19.5495548 [F08042].[JWAN8], [Expr1005], [Expr1011], [Expr1012] ? PLAN_ROW false 1 10 671 1 |--Filter(WHERE [Expr1004]=1 AND [Expr1005]<=[@LimitDate]))1 11 9 Filter Filter WHERE [Expr1004]=1 AND [Expr1005]<=[@LimitDate])? 1 0 0.000223355048 84 9.771011 [F08042].[JWAN8], [Expr1005] ? PLAN_ROW false 1 11 873 1 | |--Sort(ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC))1 12 11 Sort Sort ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC)? 253.812546 0.0112612611 0.00326276268 84 9.770788 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1004], [Expr1005] ? PLAN_ROW false 1 12 873 1 | |--Compute Scalar(DEFINE [Expr1004]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1005]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD])))))))1 13 12 Compute Scalar Compute Scalar DEFINE [Expr1004]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1005]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))))[Expr1004]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1005]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))) 253.812546 0 2.53812541E-05 84 9.756265 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1004], [Expr1005] ? PLAN_ROW false 1 13 873 1 | |--Nested Loops(Inner Join, OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH)1 14 13 Nested Loops Inner Join OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH? 253.812546 0 0.00106093648 1266 9.756239 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH] ? PLAN_ROW false 1 14 4443 1 | |--Sort(ORDER BY [F08042].[JWAN8] ASC))1 16 14 Sort Sort ORDER BY [F08042].[JWAN8] ASC)? 253.812546 0.0112612611 0.00326275057 72 9.731218 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] ? PLAN_ROW false 1 15 4443 1 | | |--Clustered Index Scan(OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0))1 17 16 Clustered Index Scan Clustered Index Scan OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0)[F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] 253.812546 8.153874 0.6184292 133 8.772304 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] ? PLAN_ROW false 1 16 873 4443 | |--Clustered Index Seek(OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD)1 18 14 Clustered Index Seek Clustered Index Seek OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD[F060116].[YAPAST], [F060116].[YAALPH] 1 0.003203401 7.9603E-05 1201 0.0237364825 [F060116].[YAPAST], [F060116].[YAALPH] ? PLAN_ROW false 253.812546 17 411 671 |--Table Spool 1 47 9 Table Spool Lazy Spool ? ? 14.6738691 0.0167567562 2.84129646E-06 19 9.778358 [Expr1011], [Expr1012] ? PLAN_ROW false 3 18 394 658 |--Filter(WHERE [F08042].[JWAN8]=[F08042].[JWAN8] AND [Expr1012]>[Expr1005]))1 49 47 Filter Filter WHERE [F08042].[JWAN8]=[F08042].[JWAN8] AND [Expr1012]>[Expr1005])? 14.6738691 0 0.000223355048 84 9.761592 [Expr1011], [Expr1012] ? PLAN_ROW false 1 19 574434 658 |--Sort(ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC))1 50 49 Sort Sort ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC)? 253.812546 0.0112612611 0.00326276268 84 9.761369 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1011], [Expr1012] ? PLAN_ROW false 1 20 574434 658 |--Compute Scalar(DEFINE [Expr1011]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1012]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD])))))))1 51 50 Compute Scalar Compute Scalar DEFINE [Expr1011]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1012]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))))[Expr1011]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1012]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))) 253.812546 0 2.53812541E-05 84 9.746845 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1011], [Expr1012] ? PLAN_ROW false 1 21 574434 658 |--Nested Loops(Inner Join, OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH)1 52 51 Nested Loops Inner Join OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH? 253.812546 0 0.00106093648 1325 9.7468195 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH] ? PLAN_ROW false 1 22 2923494 658 |--Clustered Index Scan(OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0))1 54 52 Clustered Index Scan Clustered Index Scan OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0)[F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] 253.812546 8.153874 0.6184292 133 8.772304 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] ? PLAN_ROW false 1 23 574434 2923494 |--Clustered Index Seek(OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD)1 55 52 Clustered Index Seek Clustered Index Seek OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD[F060116].[YAPAST], [F060116].[YAALPH] 1 0.003203401 7.9603E-05 1201 0.0288410634 [F060116].[YAPAST], [F060116].[YAALPH] ? PLAN_ROW false 253.812546
Broken Up Query Execution Plan:
_x0023_ Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions 1 676 1 SELECT t1.AddressNumber , t1.HistoryDate as [DateTime] , t1.DateType as value , isnull(MIN( t2.HistoryDate ),@LimitDate) as NextDateTime into #T3 FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1 LEFT JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2 ON t1.AddressNumber = t2.AddressNumber and t2.HistoryDate > t1.HistoryDate WHERE (t2.DateType = 0 or t2.HistoryDate is null) and t1.HistoryDate <= @LimitDate GROUP BY t1.AddressNumber , t1.HistoryDate , t1.DateType 1 1 0 ? ? ? ? 502.942749 ? ? ? 19.6764889 ? ? SELECT_INTO false ? 2 676 1 |--Table Insert(OBJECT [#T3]), SET[#T3].[NextDateTime]=[Expr1015], [#T3].[value]=[Expr1004], [#T3].[DateTime]=[Expr1005], [#T3].[AddressNumber]=[F08042].[JWAN8]))1 2 1 Table Insert Insert OBJECT [#T3]), SET[#T3].[NextDateTime]=[Expr1015], [#T3].[value]=[Expr1004], [#T3].[DateTime]=[Expr1005], [#T3].[AddressNumber]=[F08042].[JWAN8])? 502.942749 0.0167567562 0.00050294277 15 19.6764889 ? ? PLAN_ROW false 1 3 676 1 |--Top(ROWCOUNT est 0) 1 3 2 Top Top ? ? 502.942749 0 5.02942748E-05 35 19.65923 [F08042].[JWAN8], [Expr1004], [Expr1005], [Expr1015] ? PLAN_ROW false 1 4 676 1 |--Compute Scalar(DEFINE [Expr1015]=isnull([Expr1014], [@LimitDate])))1 4 3 Compute Scalar Compute Scalar DEFINE [Expr1015]=isnull([Expr1014], [@LimitDate]))[Expr1015]=isnull([Expr1014], [@LimitDate]) 502.942749 0 5.02942748E-05 35 19.6591778 [F08042].[JWAN8], [Expr1004], [Expr1005], [Expr1015] ? PLAN_ROW false 1 5 676 1 |--Stream Aggregate(GROUP BY [F08042].[JWAN8], [Expr1005], [Expr1004]) DEFINE[Expr1014]=MIN([Expr1012])))1 5 4 Stream Aggregate Aggregate GROUP BY [F08042].[JWAN8], [Expr1005], [Expr1004])[Expr1014]=MIN([Expr1012]) 502.942749 0 0.005495719 35 19.6591282 [F08042].[JWAN8], [Expr1004], [Expr1005], [Expr1014] ? PLAN_ROW false 1 6 760 1 |--Sort(ORDER BY [F08042].[JWAN8] ASC, [Expr1005] ASC, [Expr1004] ASC))1 6 5 Sort Sort ORDER BY [F08042].[JWAN8] ASC, [Expr1005] ASC, [Expr1004] ASC)? 1127.5127 0.0112612611 0.0179335959 35 19.6536331 [F08042].[JWAN8], [Expr1004], [Expr1005], [Expr1012] ? PLAN_ROW false 1 7 760 1 |--Filter(WHERE [Expr1011]=0 OR [Expr1012]=NULL))1 7 6 Filter Filter WHERE [Expr1011]=0 OR [Expr1012]=NULL)? 1127.5127 0 0.00103113614 47 19.6244373 [F08042].[JWAN8], [Expr1004], [Expr1005], [Expr1012] ? PLAN_ROW false 1 8 1257 1 |--Hash Match(Left Outer Join, HASH [F08042].[JWAN8])=([F08042].[JWAN8]), RESIDUAL[F08042].[JWAN8]=[F08042].[JWAN8] AND [Expr1012]>[Expr1005]))1 8 7 Hash Match Left Outer Join HASH [F08042].[JWAN8])=([F08042].[JWAN8]), RESIDUAL[F08042].[JWAN8]=[F08042].[JWAN8] AND [Expr1012]>[Expr1005])? 1171.74561 0 0.0228043366 47 19.6234055 [F08042].[JWAN8], [Expr1004], [Expr1005], [Expr1011], [Expr1012] ? PLAN_ROW false 1 9 873 1 |--Filter(WHERE [Expr1005]<=[@LimitDate]))1 10 8 Filter Filter WHERE [Expr1005]<=[@LimitDate])? 76.2167 0 0.000121946709 84 9.800342 [F08042].[JWAN8], [Expr1004], [Expr1005] ? PLAN_ROW false 1 10 873 1 | |--Sort(ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC))1 11 10 Sort Sort ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC)? 254.055649 0.0112612611 0.00326633919 84 9.80022 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1004], [Expr1005] ? PLAN_ROW false 1 11 873 1 | |--Compute Scalar(DEFINE [Expr1004]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1005]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD])))))))1 12 11 Compute Scalar Compute Scalar DEFINE [Expr1004]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1005]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))))[Expr1004]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1005]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))) 254.055649 0 2.54055649E-05 84 9.785692 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1004], [Expr1005] ? PLAN_ROW false 1 12 873 1 | |--Nested Loops(Inner Join, OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH)1 13 12 Nested Loops Inner Join OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH? 254.055649 0 0.00106195256 1266 9.785666 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH] ? PLAN_ROW false 1 13 4443 1 | |--Sort(ORDER BY [F08042].[JWAN8] ASC))1 15 13 Sort Sort ORDER BY [F08042].[JWAN8] ASC)? 254.055649 0.0112612611 0.003266327 72 9.760625 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] ? PLAN_ROW false 1 14 4443 1 | | |--Clustered Index Scan(OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0))1 16 15 Clustered Index Scan Clustered Index Scan OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0)[F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] 254.055649 8.181282 0.619219 133 8.800501 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] ? PLAN_ROW false 1 15 873 4443 | |--Clustered Index Seek(OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD)1 17 13 Clustered Index Seek Clustered Index Seek OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD[F060116].[YAPAST], [F060116].[YAALPH] 1 0.003203401 7.9603E-05 1201 0.0237562247 [F060116].[YAPAST], [F060116].[YAALPH] ? PLAN_ROW false 254.055649 16 873 1 |--Sort(ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC))1 43 8 Sort Sort ORDER BY [F060116].[YAALPH] ASC, [F08042].[JWEFTO] ASC, [F08042].[JWSEQ#] ASC)? 254.055649 0.0112612611 0.00326633919 84 9.80022 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1011], [Expr1012] ? PLAN_ROW false 1 17 873 1 |--Compute Scalar(DEFINE [Expr1011]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1012]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD])))))))1 44 43 Compute Scalar Compute Scalar DEFINE [Expr1011]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1012]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))))[Expr1011]=If ([F08042].[JWDTAI]='DST') then 1 else If ([F08042].[JWDTAI]='DT') then 0 else 0, [Expr1012]=[dbo].[tsgudf_JDEConvertNumberDateToDatetime](Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))) 254.055649 0 2.54055649E-05 84 9.785692 [F08042].[JWAN8], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH], [Expr1011], [Expr1012] ? PLAN_ROW false 1 18 873 1 |--Nested Loops(Inner Join, OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH)1 45 44 Nested Loops Inner Join OUTER REFERENCES [F08042].[JWAN8]) WITH PREFETCH? 254.055649 0 0.00106195256 1266 9.785666 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#], [F060116].[YAALPH] ? PLAN_ROW false 1 19 4443 1 |--Sort(ORDER BY [F08042].[JWAN8] ASC))1 47 45 Sort Sort ORDER BY [F08042].[JWAN8] ASC)? 254.055649 0.0112612611 0.003266327 72 9.760625 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] ? PLAN_ROW false 1 20 4443 1 | |--Clustered Index Scan(OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0))1 48 47 Clustered Index Scan Clustered Index Scan OBJECT [JDE_PROD].[PRODDTA].[F08042].[F08042_PK]), WHERE([F08042].[JWDTAI]='DST' OR [F08042].[JWDTAI]='DT') AND Convert(ltrim(rtrim(Convert([F08042].[JWHSTD]))))<>0)[F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] 254.055649 8.181282 0.619219 133 8.800501 [F08042].[JWAN8], [F08042].[JWDTAI], [F08042].[JWHSTD], [F08042].[JWEFTO], [F08042].[JWSEQ#] ? PLAN_ROW false 1 21 873 4443 |--Clustered Index Seek(OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD)1 49 45 Clustered Index Seek Clustered Index Seek OBJECT [JDE_PROD].[PRODDTA].[F060116].[F060116_PK]), SEEK[F060116].[YAAN8]=[F08042].[JWAN8]), WHERE[F060116].[YAPAST]<>'I' AND [F060116].[YAPAST]<>'T') ORDERED FORWARD[F060116].[YAPAST], [F060116].[YAALPH] 1 0.003203401 7.9603E-05 1201 0.0237562247 [F060116].[YAPAST], [F060116].[YAALPH] ? PLAN_ROW false 254.055649
-
From the plan it seems that he wants to do an ahead optimization and sort the join it actually does the join. The second one retrieve the result from t1 and matches a row in t2 so it will be much faster
try this
INSERT INTO @T2
SELECT t1.HistoryDate, isnull(MIN( t2.HistoryDate ),@LimitDate) , t1.AddressNumber , Datediff(dd,t1.HistoryDate,isnull(MIN( t2.HistoryDate ),@LimitDate) )
FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1
LEFT JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2
ON t1.AddressNumber = t2.AddressNumber and t2.HistoryDate > t1.HistoryDate
WHERE t1.DateType =1 and (t2.DateType = 0 or t2.HistoryDate is null) and t1.HistoryDate <= @LimitDate
GROUP BY t1.AddressNumber , t1.HistoryDate
or
SELECT
t1.HistoryDate,
( SELECT isnull(MIN(HistoryDate ),@LimitDate)
FROM dbo.tsgvw_JDEEmployeeHireRehireView
WHERE DateType = 0 and HistoryDate>t1.HistoryDate and AddressNumber=t1.AddressNumber)
, t1.AddressNumber
FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1
WHERE t1.DateType =1 and t1.HistoryDate <= @LimitDate --Bad query see next post : )
this will do goog if you have an index (AddressNumber,HistoryDate)
Kindest Regards,Vasc
-
the second Vasc Query should have been:
SELECT
t1.HistoryDate,
isnull(( SELECT MIN(HistoryDate )
FROM dbo.tsgvw_JDEEmployeeHireRehireView
WHERE DateType = 0 and HistoryDate>t1.HistoryDate and AddressNumber=t1.AddressNumber) ,@LimitDate)
, t1.AddressNumber
FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1
WHERE t1.DateType =1 and t1.HistoryDate <= @LimitDate
But again asuming you don't want to change the query you can check that at line 22 of the long performing query you get:
Clustered Index Scan of 2923494 rows !!!!
That is why your performance is killed! you need to convert that into an index Seek instead!
It is hard without the DDL of the tables involved in the view and the view definitions to figure out the best path without changing the query.
For the moment you need to either use the "change the query" approach recommended by Vasc or see if there are indexes that are not being used in the view!!
hth
* Noel -
Right my mistake : ) didn't check well enaugh just copy paste ...and ups there goes a LITTLE (actually big) mistake
Ya somehow the first plan force the optimizer to do the actual join and I suspect that at fault is the outside group by (wich can be dropped) and order by
I would like a time without those two in query
Kindest Regards,Vasc
-
THANK YOU BOTH for the great suggestions and help understanding all this!
I will be looking at both of your ideas this afternoon and let you know tomorrow what happens.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply