Slow Procedure

sstrauss 2017-11-30 14:27:15

I have this stored procedure that needs to be ramped up, it right now takes 8 seconds to run I need it to get down to 1

Hugo Kornelis 2017-11-30 15:06:52
Based on the posted execution plan, there are three steps in the process that consume most of the time.

1 (1.7 seconds): select @newestMonth = max((substring(MonthYear,1,2) * 12 + substring(MonthYear,3,2) -1)) from vw_AllMenusList where AccountName = @ICompany

Issues I see here are:

(1) a conversion of column Account.AccountName to nvarchar(50) which apparently is how @ICompany is declare. Change the declaration of @ICompany in the stored proc to match the data type in the table and you might get better performance and better estimates.

(2) the way you wrote the aggregation makes it impossible for the optimizer to reduce work. If you rewrite as substring(max(MonthYear),1,2) * 12) + substring(max(MonthYear),3,2) – 1, it is probably faster. (I would also add explicit casting to int but that will not affect performance)

(3) The view appears to join some pretty big tables, which contributes to the cost of this query. The changes above might help the optimizer find a better way but that's hard to predict without knowing the definition of the view. It's also possible that entire branches can be removed from the plan if there were trusted constraints in your data – but again, hard to predict.

(I need to go now, run an errand. I'll be back with some comments on the other two slow statements later)

EDIT: In suggestion 2 above, it's probably even possible to reduce the logic and complexity a bit further: CAST(LEFT(MAX(MonthYear), 4) AS int) – 1. And if MonthYear is always four characters (as the name implies), then you don't need the LEFT function either: CAST(MAX(MonthYear) AS int) – 1.


Thanks for posting the view code!

That PIVOT is indeed nasty and I understand that you want to get rid of it. But in the execution plan I see that the optimizer realizes you use none of the columns it produces, so the entire pivot operation is just ignored for this execution plan. That being said, the optimizer does still have to join all MenuAttribute rows to the Attribute rows, resulting in an intermediate set of more than a million rows. Which is also more than the optimizer expects, resulting in data spilling to tempdb.

Instead of trying to solve that, how about eliminating the use of these two tables completely? The view itself includes a lot of columns created by the pivot, but these are not used in your query. What remains is the p.MenuID value, which serves as the linking pin for joining the menu and deltatemptable tables. This can be very important, if there are values for MenuID that do appear in those two but not in the [menu]attribute tables. But if that is not the case, then you can just as well join the two other tables directly.

So long story short. If there can be cases where you have a value x in menuid in both tables menu and deltatemptable, but not in the join results of menuattribute and attribute, then the view needs to remain as it now is; however if that is not the case than I recommend changing the last ON clause from p.menuid = f.menuid to a.menuid = f.menuid. My expectation is that this will result in an immediate and enormous speedup af at least this first query, but probably the others as well. (I have not studies the others in detail yet, but I do see that the same view is used in them).

If you do not know if the above scenario can oor cannot happen in your data, then you can consider making a new view identical to the old but with the change I suggested and then compare them (EXCEPT is a great keyword for that). However, keep in mind that this is not a 100% certain proof: things that are not in your data today might enter your data tomorrow, unless there are business rules and/or constraints to prevent them.

(Because the view is used in all three long-running queries, I will not look at the others in detail until after you have tried this change. Or verified and confirmed that you cannot use it based on your data and business rules)

sstrauss 2017-11-30 15:14:41
here is the view, i wish i could get rid of the pivot but how the table is and the data is needed i cant.

SELECT a.CompanyID, b.AccountName, p.*, a.FileLocation, f.similarMenuID, f.MonthYear
(SELECT menuid, name, value
FROM menuattribute, attribute
WHERE menuattribute.attributeid = attribute.attributeid) m PIVOT (max(value) FOR name IN ([Region], [Class Name], [Flight Number], [Group], [FName], [Title], [Description], [Disabled], [Type], [Category], [Cycle], [Departure],
[Arrival], [LotNumber], [cpn], [route], [mealCode])) p ON a.MenuID = p.MenuID LEFT JOIN
account b ON b.customerid = a.CompanyID LEFT JOIN
deltatemptable f ON p.menuid = f.menuid