I have inherited this T-SQL query.  When the query is run and there are few items to return, we get good performance from this query.  However, when this query is run and there is a larger list (maybe 50 rows), then it takes over two seconds to run and will fail at times with a locking error.  I have tried to tweak this query but it still is having problems.  Any suggestions or help to tighten this up or make some changes for us.  Any help is appreciated.

USE [WZMES]
GO
/****** Object:  StoredProcedure [dbo].[mesdb_GetIngotHistory]    Script Date: 07/21/2015 16:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[mesdb_GetIngotHistory]
@IngotNum nvarchar(50)
WITH EXEC AS CALLER
AS
with PieceList as (
select ingot.wo_id, ingot.oper_id, ingot.seq_no, ingot.lot_no, ingot.item_id, ingot.ent_id, ingot.fg_lot_no, 1 as level
from mesdb.dbo.item_cons as ingot
where item_id like '%Ingot%' 
and ingot.lot_no like @IngotNum + '%'
and ingot.fg_lot_no not like '%End%'
union all
select child.wo_id, child.oper_id, child.seq_no, child.lot_no, child.item_id, child.ent_id, child.fg_lot_no , p.level + 1
from mesdb.dbo.item_cons child
inner join PieceList as p on p.fg_lot_no = child.lot_no
where child.lot_no <> child.fg_lot_no
and child.fg_lot_no not like '%End%'
)
select pl.fg_lot_no as Piece, i.item_id as Item, e.ent_name as Location, i.oper_id as LastOperation, i.date_in_local as LastDate, i.wo_id as WorkOrder
from PieceList pl join mesdb.dbo.item_inv i on pl.fg_lot_no = i.lot_no join mesdb.dbo.ent e on i.ent_id = e.ent_id
order by case
when i.item_id like '%Ingot' then 1
when i.item_id like '%Log' then 2
else 3
end,
case 
when i.item_id like '%Ingot' THEN
case substring(fg_lot_no, charindex('-', fg_lot_no) + 1,1)
when 'A' THEN 1
when 'B' THEN 2
when 'C' THEN 3
when 'D' THEN 4
when 'E' THEN 5
when 'F' THEN 6
end
when i.item_id like '%Log' THEN
case substring(fg_lot_no, charindex('-', fg_lot_no) + 1,1)
when 'A' THEN 1
when 'B' THEN 2
when 'C' THEN 3
when 'D' THEN 4
when 'E' THEN 5
when 'F' THEN 6
when 'G' THEN 7
when 'H' THEN 8
when 'I' THEN 9
end
ELSE case substring(fg_lot_no, charindex('-', fg_lot_no) + 1,1)
when 'T' THEN 1
when 'M' THEN 2
when 'B' THEN 3
end
end , fg_lot_no

• Changed type Naomi NModerator 10 hours 58 minutes ago question

This seems to be a recursive CTE query with quite strange order by and overly-complex.

My suggestions.

1. Try to get the first part of the query into a #temp table and use that table in the first part of the recursive cte.

2. Add the case item_id like ... as a calculated column inside your query (say, SortOrder).

3. You can also get the part after - into a separate column as well. You only need to apply special ordering for T/M/B case as otherwise it's already ordered correctly and you don't need your case expressions.

For avoding the locking error you would normally an appropriate index. But your usage of '%pattern%' prohibits the usage of an index on the columns item_id and fg_lot_no.

So you may try to index the other columns properly to get a covering index.

Yeah this is what I found out doing a trace and then a execution plan.  Problem is, we can't index the other table because it may violate our warranty on the product we are querying.  The temp table option Naomi suggested might be an option as well. 

Sorry  I am slow what is T/M/B case?

This one

ELSE case substring(fg_lot_no, charindex('-', fg_lot_no) + 1,1)
when 'T' THEN 1
when 'M' THEN 2
when 'B' THEN 3
end

The rest of the cases are redundant as you can simply order by that character, no need to translate into number as the order is already correct. This is the only case when order is not natural order.

Sorry, long day.  I can see what you are saying now.  I have user breathing down my neck and management wondering what is going on.  Again, thanks for your help.  I'll post my findings here in case anyone else runs into a similar problem.

Thanks.

Problem is, we can't index the other table because it may violate our warranty on the product we are querying. 

Normally adding an index won't hurt anything but I can understand your concerns. Have you thought about contacting the vendor to see if it's OK or if they can "hotfix" this for you?

Have you considered an indexed view for the table?