mike.dinnis (4/25/2016)
Jacob Wilkins (4/25/2016)
You'll either need to look at simplifying the query (maybe explicitly querying the underlying tables from the view that you need instead of forcing the optimizer to figure it out), simplifying/tuning the code underlying the view, or just accepting that if that query's plan needs to be recompiled, the first run will take about 5 extra seconds due to compile time.Cheers!
Thanks for the tips.
Unfortunately for me, each time I call the query from my app it's with a different WHERE clause value and so recompiles every time.
I think I need to follow the route of simplification if at all possible.
Then do this so that new values will be able to reuse the previously compiled plan.
declare @var varchar(20) = '1604-660'
SELECT cc.fchrCartonRef AS 'carton_ref'
FROM dbo.tblCartonContents AS cc WITH (NOLOCK)
JOIN cs3plc.scheme.rvShipOrderDetail AS sod
ON cc.fchrOrderNo = sod.order_no
--AND RIGHT(' ' + CONVERT(VARCHAR, cc.fnOrderLine), 4) = sod.order_line_no
WHERE cc.fchrCartonRef = @var