test.sql

-- The query takes about 5s
-- In comments I explain a few changes that can bring it down to <1s
with
orderLines as (
    select r.DocEntry as rdrDocId,
           r1.LineNum as rdrLineId
    from rdr1 r1
    join ordr r on r.DocEntry = r1.DocEntry
	-- If I filter only by LineStatus or only by DocStatus here, query takes <1s
    where r1.LineStatus = 'O' and r.DocStatus = 'O'
),
picklistDetails as (
    select U_KommNr as pklDocId,
           max(cast(U_Space as int)) as maxPlace
    from [@PICKING]
	where U_DeletedF = 'N'
    group by U_KommNr
),
picklistDocs as (
    select p.AbsEntry      as pklDocId,
	       -- If I remove the following reference to pd, query takes <1s
           case
		   when pd.maxPlace is null then 0
		   else pd.maxPlace
           end             as pklDocMaxPlace
    from opkl p
    left join picklistDetails pd on pd.pklDocId = p.AbsEntry
),
picklistDocLines as (
	select AbsEntry   as pklDocId,
	       PickEntry  as pklLineId,
		   OrderEntry as rdrDocId,
		   OrderLine  as rdrLineId
	from PKL1
)
-- If I select anything else than this, query takes <1s
select p.pklDocMaxPlace
from picklistDocs p
join picklistDocLines p1 on p.pklDocId = p1.pklDocId
join orderLines r1 on r1.rdrDocId = p1.rdrDocId
                  and r1.rdrLineId = p1.rdrLineId
-- If I force parallelism by using the following option, query takes <1s
--option(querytraceon 8649)