Thanks for the code Jacob, I will study it tomorrow. I did not know that the estimated EP was just an estimation :Whistling: and that it could be different at execution time.
I don't want to load the server with tests while users are working but over the week end I can run it with the old code and get the actual plan.
But in the meantime, I optimized the view. I must have created it before "discovering" the isnull() function because I am looking at the code and I am totally unimpressed with my own work :-D. Why did I use a subquery ??
I changed the code to
Select it.Warehouse, si.Style, si.Label, si.Color, si.Dimension, si.Size, sum(it.invtrxqty) TransactionTotal,isnull(i.QOHQty,0) OnHand
fromInvTransactions it
joinStyleItem si on si.ItemNo = it.ItemNo
left outer join Inventory i on i.itemno = it.itemno and i.warehouse = it.warehouse
joinStyle s on s.style = it.style
wheres.styletype='FG'
Group by
it.warehouse, si.Style, si.Label, si.Color, si.Dimension, si.Size, i.QohQty
The estimated execution plan still shows nested loops and a sort (that accounts for 46%) but the execution of if (select count(*)....) >0 now takes the same time as putting the count in a variable so the subquery clearly plays a role in this.