July 13, 2021 at 9:19 pm
As part of testing I removed the JobHead and JobAsmbl tables from both SubQuery statements but the execution jumped from 00:00:01 to return 28,800 records to 00:00:06.
The JobOper and JobMtl tables hold the fields - JobNum and AssemblySeq allowing me to join.
Yes - you need those tables in the CTE so you can return the columns and build the join. The OUTER APPLY doesn't need those tables - because the relationship is done in the where clause.
For testing - you would leave the Subquery3 CTE in place - and right after that and before the next join you put the outer apply.
SubQuery3 AS sq3 ON jh.Company = sq3.Company AND jh.JobNum = sq3.JobNum
OUTER APPLY (
SELECT COUNT(jm.PartNum) AS ComponentParts
FROM [ERP10-Live].Erp.JobMtl AS jm3
INNER JOIN [ERP10-Live].Erp.Part AS p3 ON p3.Company = jm3.Company AND p3.PartNum = jm3.PartNum
WHERE jm3.Company = ja.Company
AND jm3.JobNum = ja.JobNum
AND jm3.AssemblySeq = ja.AssemblySeq
AND p3.SearchWord <> 'JIG/FIXT'
AND p3.TypeCode = 'M'
) As sq3_1
[ERP10-Live].dbo.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq
In the SELECT you can then compare the value returned from the join vs the value returned from the outer apply. You can then change the alias on the outer apply and comment out the Subquery3 join and test for performance.
If you reformatted the code to be easier to read - it would help identify where to make changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2021 at 11:24 pm
Apologies - not sure where to place:
WITH SubQuery AS
(
SELECTjh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode,
CAST(CASE WHEN NOT jh.Date03 IS NULL THEN jh.Date03 WHEN jh.Number01 <> 0 THEN DATEADD(d, - jh.Number01, jo.StartDate) ELSE jo.StartDate END AS DateTime) AS jhPaintDate,
CASE WHEN NOT jh.Date03 IS NULL THEN 1 ELSE 0 END AS Planned
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jo.OpCode IN ('71', '81', '82')) AND (jh.JobReleased = 1) AND (jo.OpComplete = 0)
),
SubQuery2 AS
(
SELECT jh.Company, jm.JobNum, jm.AssemblySeq,
SUM(CASE WHEN (p.TypeCode = 'M' AND (jm.RequiredQty - jm.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS MShortages,
SUM(CASE WHEN (p.TypeCode = 'P' AND (jm.RequiredQty - jm.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS PShortages, jh.DueDate
FROM [ERP10-Live].Erp.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON ja.Company = jh.Company AND ja.JobNum = jh.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq INNER JOIN
[ERP10-Live].Erp.Part AS p ON p.Company = jm.Company AND p.PartNum = jm.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON pq.Company = p.Company AND pq.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jm.IssuedComplete = 0) AND (p.SearchWord <> 'JIG/FIXT')
GROUP BY jm.JobNum, jh.Company, jm.AssemblySeq, jh.DueDate
),
SubQuery3 AS
(
SELECT jh.Company, jm.JobNum, jm.AssemblySeq,
COUNT(jm.PartNum) AS ComponentParts
FROM [ERP10-Live].Erp.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON ja.Company = jh.Company AND ja.JobNum = jh.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq INNER JOIN
[ERP10-Live].Erp.Part AS p ON p.Company = jm.Company AND p.PartNum = jm.PartNum
WHERE (jh.JobComplete = 0) AND (p.SearchWord <> 'JIG/FIXT') AND (p.TypeCode = 'M')
GROUP BY jm.JobNum, jh.Company, jm.AssemblySeq)
SELECT jh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, ja.FinalOpr, jo.EstSetHours, jo.EstProdHours, jo.ProdStandard, jo.StdFormat,
CAST(jo.EstSetHours + jo.EstProdHours AS decimal(12, 2)) AS OpTime, jh.JobComplete, jh.JobClosed, jh.JobReleased, jh.JobFirm, CAST(ja.StartDate AS datetime) AS jaStartDate, CAST(ja.DueDate AS datetime) AS jaDueDate,
CAST(jo.LastLaborDate AS datetime) AS LastLaborDate, CAST(jo.RunQty AS int) AS joRunQty, CAST(jo.QtyCompleted AS int) AS joQtyCompleted, jo.OpComplete, CAST(jh.ProdQty AS int) AS ProdQty,
CAST(jh.QtyCompleted AS int) AS jhQtyCompleted, ja.PartNum, p.ProdCode, p.SearchWord, p.OnHoldReason_c, p.CheckBox02 AS Engineering, p.CheckBox04 AS Programming, p.OnHold, jo.SetupComplete,
CAST(jo.StartDate AS datetime) AS joStartDate, jo.StartHour AS joStartHour, CAST(jo.DueDate AS DATETIME) AS joDueDate, CAST(jh.CreateDate AS datetime) AS CreateDate, ja.TLALaborCost, ja.TLAMaterialCost,
ja.TLASubcontractCost, ja.LLALaborCost, ja.LLAMaterialCost, ja.LLASubcontractCost, CAST(jh.ReqDueDate AS datetime) AS jhReqDueDate, jh.Date01 AS jhScheduleDate,
CAST(jh.Date05 AS DateTime) AS jhWeldDate, CAST(jh.Date06 AS DateTime) AS jhInsertDate, CAST(jh.Date04 AS DateTime) AS jhAssemblyDate, jh.ShortChar03 AS Operator_Weld,
jh.ShortChar06 AS Operator_Insert, jh.ShortChar05 AS Operator_Assy, jh.ShortChar04 AS WeldComments, jo.DaysOut, CAST(jh.DueDate AS datetime) AS jhDueDate, jo.ActSetupHours, jo.ActProdHours,
CAST(ja.RequiredQty - ja.PullQty AS int) AS ProductionQty, jo.VendorNum, jh.Character01 AS jhComments, CAST(jh.Number01 AS int) AS jhExpedite, jh.ShortChar01 AS jhQJob,
jh.ShortChar02 AS jhLifecycle, CAST(ja.RequiredQty AS int) AS RequiredQty, CAST(ja.PullQty AS int) AS PullQty, p.ShortChar02 AS pMatType, p.ShortChar03 AS pMatThick,
jo.CommentText, jo.ShortChar01 AS ToolTape, jo.Character02 AS QualityAlert, jo.Character03 AS MesFeedback, CASE WHEN NOT jh.Date01 IS NULL
THEN jh.Date01 - 365 WHEN NOT jh.Date05 IS NULL THEN jh.Date05 WHEN jh.Number01 <> 0 THEN DATEADD(d, jh.Number01, jo.StartDate) ELSE jo.StartDate END AS OpDate, Powder,
sq.jhPaintDate,
IsNull(sq2.MShortages,0) AS MShortages, IsNull(sq2.PShortages,0) AS PShortages
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum LEFT OUTER JOIN
SubQuery AS sq ON jh.Company = sq.Company AND jh.JobNum = sq.JobNum LEFT OUTER JOIN
SubQuery2 AS sq2 ON jh.Company = sq2.Company AND jh.JobNum = sq2.JobNum INNER JOIN
[ERP10-Live].dbo.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq OUTER APPLY
(
SELECT jm.PartNum AS Powder
FROM [ERP10-Live].Erp.JobMtl jm
WHERE ja.Company = Company
AND ja.JobNum = JobNum
AND ja.AssemblySeq = AssemblySeq
AND (jm.PartNum LIKE 'POW%')
) AS jm
LEFT OUTER JOIN
[ERP10-Live].dbo.Part AS p ON ja.Company = p.Company AND ja.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jh.ProdQty - jh.QtyCompleted > 0)
July 14, 2021 at 5:02 am
Let's peel one potato at a time. The first stop is the last item in your code...
(jh.ProdQty - jh.QtyCompleted > 0)
Do you see any problems with that bit o' code?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2021 at 7:49 am
From a performance point of view I'm honestly not sure but from a logical point of view this can be simplified to jh.ProdQty > jh.QtyCompleted.
July 14, 2021 at 9:33 am
Update - figured out where to place the code snippet:
Using the Outer Apply 28,000 records 00:00:01
Using the SubQuery 28,000 records 00:00:02
July 14, 2021 at 11:24 am
Update - I have rewritten SubQuery2 and SubQuery3 as OUTER APPLY and tested.
Results set is correct and execution is 00:00:01.
The removal of the last cte is proving more problematic and I suspect it's cumbersome because I am referencing the JobOper table within the SubQuery and in the main select statement.
WITH SubQuery AS
(
SELECTjh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode,
CAST(CASE WHEN NOT jh.Date03 IS NULL THEN jh.Date03 WHEN jh.Number01 <> 0 THEN DATEADD(d, - jh.Number01, jo.StartDate) ELSE jo.StartDate END AS DateTime) AS jhPaintDate,
CASE WHEN NOT jh.Date03 IS NULL THEN 1 ELSE 0 END AS Planned
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jo.OpCode IN ('71', '81', '82')) AND (jh.JobReleased = 1) AND (jo.OpComplete = 0)
)
SELECT jh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, ja.FinalOpr, jo.EstSetHours, jo.EstProdHours, jo.ProdStandard, jo.StdFormat,
CAST(jo.EstSetHours + jo.EstProdHours AS decimal(12, 2)) AS OpTime, jh.JobComplete, jh.JobClosed, jh.JobReleased, jh.JobFirm, CAST(ja.StartDate AS datetime) AS jaStartDate, CAST(ja.DueDate AS datetime) AS jaDueDate,
CAST(jo.LastLaborDate AS datetime) AS LastLaborDate, CAST(jo.RunQty AS int) AS joRunQty, CAST(jo.QtyCompleted AS int) AS joQtyCompleted, jo.OpComplete, CAST(jh.ProdQty AS int) AS ProdQty,
CAST(jh.QtyCompleted AS int) AS jhQtyCompleted, ja.PartNum, p.ProdCode, p.SearchWord, p.OnHoldReason_c, p.CheckBox02 AS Engineering, p.CheckBox04 AS Programming, p.OnHold, jo.SetupComplete,
CAST(jo.StartDate AS datetime) AS joStartDate, jo.StartHour AS joStartHour, CAST(jo.DueDate AS DATETIME) AS joDueDate, CAST(jh.CreateDate AS datetime) AS CreateDate, ja.TLALaborCost, ja.TLAMaterialCost,
ja.TLASubcontractCost, ja.LLALaborCost, ja.LLAMaterialCost, ja.LLASubcontractCost, CAST(jh.ReqDueDate AS datetime) AS jhReqDueDate, jh.Date01 AS jhScheduleDate,
CAST(jh.Date05 AS DateTime) AS jhWeldDate, CAST(jh.Date06 AS DateTime) AS jhInsertDate, CAST(jh.Date04 AS DateTime) AS jhAssemblyDate, jh.ShortChar03 AS Operator_Weld,
jh.ShortChar06 AS Operator_Insert, jh.ShortChar05 AS Operator_Assy, jh.ShortChar04 AS WeldComments, jo.DaysOut, CAST(jh.DueDate AS datetime) AS jhDueDate, jo.ActSetupHours, jo.ActProdHours,
CAST(ja.RequiredQty - ja.PullQty AS int) AS ProductionQty, jo.VendorNum, jh.Character01 AS jhComments, CAST(jh.Number01 AS int) AS jhExpedite, jh.ShortChar01 AS jhQJob,
jh.ShortChar02 AS jhLifecycle, CAST(ja.RequiredQty AS int) AS RequiredQty, CAST(ja.PullQty AS int) AS PullQty, p.ShortChar02 AS pMatType, p.ShortChar03 AS pMatThick,
jo.CommentText, jo.ShortChar01 AS ToolTape, jo.Character02 AS QualityAlert, jo.Character03 AS MesFeedback, CASE WHEN NOT jh.Date01 IS NULL
THEN jh.Date01 - 365 WHEN NOT jh.Date05 IS NULL THEN jh.Date05 WHEN jh.Number01 <> 0 THEN DATEADD(d, jh.Number01, jo.StartDate) ELSE jo.StartDate END AS OpDate, Powder,
sq.jhPaintDate,
MShortages,
PShortages,
ComponentParts
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum LEFT OUTER JOIN
SubQuery AS sq ON jh.Company = sq.Company AND jh.JobNum = sq.JobNum OUTER APPLY
(
SELECT COUNT(jm.PartNum) AS ComponentParts
FROM [ERP10-Live].Erp.JobMtl jm INNER JOIN
[ERP10-Live].Erp.Part AS p ON jm.Company = p.Company AND jm.PartNum = p.PartNum
WHERE ja.Company = jm.Company
AND ja.JobNum = jm.JobNum
AND ja.AssemblySeq = jm.AssemblySeq
AND p.SearchWord <> 'JIG/FIXT'
AND p.TypeCode = 'M'
) As jm
OUTER APPLY
(
SELECT jm1.PartNum AS Powder
FROM [ERP10-Live].Erp.JobMtl jm1
WHERE ja.Company = Company
AND ja.JobNum = JobNum
AND ja.AssemblySeq = AssemblySeq
AND (PartNum LIKE 'POW%')
) AS jm1
OUTER APPLY
(
SELECT
SUM(CASE WHEN (p.TypeCode = 'M' AND (jm2.RequiredQty - jm2.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS MShortages,
SUM(CASE WHEN (p.TypeCode = 'P' AND (jm2.RequiredQty - jm2.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS PShortages
FROM [ERP10-Live].Erp.JobMtl AS jm2 INNER JOIN
[ERP10-Live].Erp.Part AS p ON jm2.Company = p.Company AND jm2.PartNum = p.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON p.Company = pq.Company AND p.PartNum = pq.PartNum
WHERE ja.Company = jm2.Company
AND ja.JobNum = jm2.JobNum
AND ja.AssemblySeq = jm2.AssemblySeq
AND jm2.IssuedComplete = 0
AND p.SearchWord <> 'JIG/FIXT'
GROUP BY jm2.Company, jm2.JobNum, jm2.AssemblySeq
) AS jm2
INNER JOIN
[ERP10-Live].dbo.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq LEFT OUTER JOIN
[ERP10-Live].dbo.Part AS p ON ja.Company = p.Company AND ja.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jh.ProdQty > jh.QtyCompleted)
July 14, 2021 at 1:56 pm
Update - all ctes removed - execution 00:00:02
SELECT jh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, ja.FinalOpr, jo.EstSetHours, jo.EstProdHours, jo.ProdStandard, jo.StdFormat,
CAST(jo.EstSetHours + jo.EstProdHours AS decimal(12, 2)) AS OpTime, jh.JobComplete, jh.JobClosed, jh.JobReleased, jh.JobFirm, CAST(ja.StartDate AS datetime) AS jaStartDate, CAST(ja.DueDate AS datetime) AS jaDueDate,
CAST(jo.LastLaborDate AS datetime) AS LastLaborDate, CAST(jo.RunQty AS int) AS joRunQty, CAST(jo.QtyCompleted AS int) AS joQtyCompleted, jo.OpComplete, CAST(jh.ProdQty AS int) AS ProdQty,
CAST(jh.QtyCompleted AS int) AS jhQtyCompleted, ja.PartNum, p.ProdCode, p.SearchWord, p.OnHoldReason_c, p.CheckBox02 AS Engineering, p.CheckBox04 AS Programming, p.OnHold, jo.SetupComplete,
CAST(jo.StartDate AS datetime) AS joStartDate, jo.StartHour AS joStartHour, CAST(jo.DueDate AS DATETIME) AS joDueDate, CAST(jh.CreateDate AS datetime) AS CreateDate, ja.TLALaborCost, ja.TLAMaterialCost,
ja.TLASubcontractCost, ja.LLALaborCost, ja.LLAMaterialCost, ja.LLASubcontractCost, CAST(jh.ReqDueDate AS datetime) AS jhReqDueDate, jh.Date01 AS jhScheduleDate,
CAST(jh.Date05 AS DateTime) AS jhWeldDate, CAST(jh.Date06 AS DateTime) AS jhInsertDate, CAST(jh.Date04 AS DateTime) AS jhAssemblyDate, jh.ShortChar03 AS Operator_Weld,
jh.ShortChar06 AS Operator_Insert, jh.ShortChar05 AS Operator_Assy, jh.ShortChar04 AS WeldComments, jo.DaysOut, CAST(jh.DueDate AS datetime) AS jhDueDate, jo.ActSetupHours, jo.ActProdHours,
CAST(ja.RequiredQty - ja.PullQty AS int) AS ProductionQty, jo.VendorNum, jh.Character01 AS jhComments, CAST(jh.Number01 AS int) AS jhExpedite, jh.ShortChar01 AS jhQJob,
jh.ShortChar02 AS jhLifecycle, CAST(ja.RequiredQty AS int) AS RequiredQty, CAST(ja.PullQty AS int) AS PullQty, p.ShortChar02 AS pMatType, p.ShortChar03 AS pMatThick,
jo.CommentText, jo.ShortChar01 AS ToolTape, jo.Character02 AS QualityAlert, jo.Character03 AS MesFeedback, CASE WHEN NOT jh.Date01 IS NULL
THEN jh.Date01 - 365 WHEN NOT jh.Date05 IS NULL THEN jh.Date05 WHEN jh.Number01 <> 0 THEN DATEADD(d, jh.Number01, jo.StartDate) ELSE jo.StartDate END AS OpDate, Powder,
jhPaintDate,
MShortages,
PShortages,
ComponentParts
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum OUTER APPLY
(
SELECT COUNT(jm.PartNum) AS ComponentParts
FROM [ERP10-Live].Erp.JobMtl jm INNER JOIN
[ERP10-Live].Erp.Part AS p ON jm.Company = p.Company AND jm.PartNum = p.PartNum
WHERE ja.Company = jm.Company
AND ja.JobNum = jm.JobNum
AND ja.AssemblySeq = jm.AssemblySeq
AND p.SearchWord <> 'JIG/FIXT'
AND p.TypeCode = 'M'
) As jm
OUTER APPLY
(
SELECT jm1.PartNum AS Powder
FROM [ERP10-Live].Erp.JobMtl jm1
WHERE ja.Company = Company
AND ja.JobNum = JobNum
AND ja.AssemblySeq = AssemblySeq
AND (PartNum LIKE 'POW%')
) AS jm1
OUTER APPLY
(
SELECT
SUM(CASE WHEN (p.TypeCode = 'M' AND (jm2.RequiredQty - jm2.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS MShortages,
SUM(CASE WHEN (p.TypeCode = 'P' AND (jm2.RequiredQty - jm2.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS PShortages
FROM [ERP10-Live].Erp.JobMtl AS jm2 INNER JOIN
[ERP10-Live].Erp.Part AS p ON jm2.Company = p.Company AND jm2.PartNum = p.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON p.Company = pq.Company AND p.PartNum = pq.PartNum
WHERE ja.Company = jm2.Company
AND ja.JobNum = jm2.JobNum
AND ja.AssemblySeq = jm2.AssemblySeq
AND jm2.IssuedComplete = 0
AND p.SearchWord <> 'JIG/FIXT'
GROUP BY jm2.Company, jm2.JobNum, jm2.AssemblySeq
) AS jm2
OUTER APPLY
(
SELECT TOP 1
CAST(CASE WHEN NOT jh.Date03 IS NULL THEN jh.Date03 WHEN jh.Number01 <> 0 THEN DATEADD(d, - jh.Number01, jo1.StartDate) ELSE jo1.StartDate END AS DateTime) AS jhPaintDate,
CASE WHEN NOT jh.Date03 IS NULL THEN 1 ELSE 0 END AS Planned
FROM [ERP10-Live].Erp.JobOper AS jo1
WHERE ja.Company = jo1.Company
AND ja.JobNum = jo1.JobNum
AND ja.AssemblySeq = jo1.AssemblySeq
AND (jo1.OpCode IN ('71', '81', '82'))
AND (jo1.OpComplete = 0)
) AS jo1
INNER JOIN
[ERP10-Live].dbo.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq LEFT OUTER JOIN
[ERP10-Live].dbo.Part AS p ON ja.Company = p.Company AND ja.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jh.ProdQty > jh.QtyCompleted)
July 14, 2021 at 3:17 pm
In the outer apply for 'jm2' you can remove the group by - it isn't needed here. In the outer apply for 'jo1' you need to add an order by for the TOP 1 to make sure you get the same row returned every time it is called.
With testing - it appears that using outer apply for the last subquery CTE isn't as performant. You could leave that as a CTE - and add your additional summations and test/validate results and performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2021 at 3:22 pm
Thank you for all the inputs - the execution time on this and a number of other queries has been greatly improved.
It seems that there is not always 1 solution and sometimes for me it is trial an error until I get an execution time that is workable.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply