Our warehouse management package has a bottleneck (one of many) with a stored procedure and the major slowdown is due to the query that produces this execution plan.
https://www.brentozar.com/pastetheplan/?id=HkNg65elP
The stored proc can take anywhere from 3 to 10 seconds to run which is quite slow in the context of the business process it runs in.
Some additional info: Yes, there is one table where a full table scan is being done, but this table is narrow, and only has 76 rows. The query does some left joins and some sorting which is needed to produce the correct top result. Overall, it is a bit of a "Rube Goldberg" type query and could probably be simplified, but my goal is to see if it is possible to help with some indexing (which I've done and it's helped a little) or even some small tweaks to the query if needed.
In the end, I need to know where to focus next based on the plan.
Here is the query:
SELECT TOP 1 loc.location_id, loc.wh_id
FROM t_item_uom itu WITH (NOLOCK)
INNER JOIN t_class_loca clc WITH (NOLOCK)
ON itu.wh_id = clc.wh_id
AND ISNULL(dbo.usf_get_item_class_dia_ovrd ('13098271', '895', itu.uom, NULL), itu.class_id) = clc.class_id
INNER JOIN t_location loc WITH (NOLOCK)
ON clc.wh_id = loc.wh_id
AND clc.location_id = loc.location_id
INNER JOIN t_pick_area pka WITH (NOLOCK)
ON pka.pick_area = loc.pick_area
AND pka.wh_id = loc.wh_id
AND (pka.pick_area <> N'LABEL' OR (pka.pick_area = N'LABEL' AND 0 IS NULL AND 0 IS NULL) )
AND (pka.pick_area_type = 'R' OR (pka.pick_area_type = 'V' and 0 IS NULL) )
INNER JOIN t_zone_loca zlc WITH (NOLOCK)
ON loc.wh_id = zlc.wh_id
AND loc.location_id = zlc.location_id
INNER JOIN (
SELECT loc.wh_id, loc.pnd_location_id --, loc.location_id
FROM t_location loc with (nolock)
inner join t_class_loca clc WITH (NOLOCK)
on clc.location_id = loc.location_id
and clc.wh_id = loc.wh_id
and clc.class_id = 'APPAREL'
LEFT JOIN t_stored_item sto with (nolock)
ON sto.put_away_location = loc.location_id
AND sto.wh_id = loc.wh_id --BTH 20160907 missing wh_id
AND sto.put_away_location IS NOT NULL
AND sto.type = 0
WHERE loc.type in ('I','M')
AND loc.pnd_location_id IS NOT NULL --BTH 20160907 - remove from having clause, add here
GROUP BY loc.wh_id, loc.pnd_location_id, loc.c3
HAVING ((COUNT(sto.hu_id) < 100
and loc.pnd_location_id IS NOT NULL --BTH 201600907
and c3 is null)
OR (COUNT(sto.hu_id) < 500 --and loc.pnd_location_id IS NOT NULL --BTH 201600907
and c3 = 'BULK'))
) as pnd
ON pnd.wh_id = loc.wh_id
AND pnd.pnd_location_id = loc.pnd_location_id
LEFT OUTER JOIN t_put_rules_empty_and_unalloc_locs_by_pnd tpr WITH (NOLOCK)
ON tpr.pnd_location_id = loc.pnd_location_id
AND tpr.class_id = itu.class_id
AND tpr.wh_id = loc.wh_id
LEFT OUTER JOIN t_work_q q WITH(NOLOCK)
ON q.location_id = loc.location_id
AND q.wh_id = loc.wh_id
AND q.work_type = '08'
AND q.work_status = 'U'
WHERE loc.status = 'E'
AND ISNULL(q.work_q_id, 0) = 0
AND ( loc.c3 is null or loc.c3 not in ('R','H','S'))
AND (
(
loc.type = 'M'
AND ( (SELECT TOP 1 max_sku_count
FROM t_zone zone2 (NOLOCK)
WHERE zone2.wh_id = loc.wh_id
AND loc.zone = zone2.zone) >
(SELECT COUNT(sto2.item_number)
FROM t_stored_item sto2 (NOLOCK)
WHERE loc.wh_id = sto2.wh_id
AND loc.location_id = sto2.location_id
)
OR '13098271' IN (SELECT sto2.item_number
FROM t_stored_item sto2 (NOLOCK)
WHERE loc.wh_id = sto2.wh_id
AND loc.location_id = sto2.location_id
)
)
)
OR (loc.type = 'I' AND itu.unit_volume = 0 AND itu.nested_volume = 0)
OR (loc.type = 'I' AND loc.capacity_volume = 0)
OR (loc.type = 'I' AND loc.capacity_volume >= 0 +
(CASE
WHEN 0 = 0 THEN 0
ELSE 0
END * (1 - 1)
)
)
)
--Ensure that only one item is designated to the location
AND (loc.type = 'M'
OR (loc.type = 'I' AND NOT EXISTS (SELECT 1
FROM t_stored_item sto2 WITH (NOLOCK) -- Sum the item in the location to determine volume
WHERE sto2.wh_id = loc.wh_id
AND sto2.put_away_location = loc.location_id)))
AND itu.wh_id = '895'
AND itu.item_number = '13098271'
AND clc.class_id = 'APPAREL'
AND zlc.zone = 'ALL'
ORDER BY
tpr.percent_empty_and_unalloc DESC,
loc.type,
loc.user_count,
loc.picking_flow,
loc.location_id