February 4, 2014 at 1:09 pm
Hi folks,
I have a stored procedure that's returning a little over 2000 rows, and takes about 90 seconds to run. It's not run often; probably only a couple times/day, but I'd like to see it run faster (and so would the users 😀 ).
Based on the execution plan, this insert is taking about half the cost:
;INSERT INTO #maintType (CartID, MaintenanceOption)
SELECT c.CartID
, STUFF((SELECT ', ' + cam.MaintenanceOption
FROM CartActionMaintenance cam
INNER JOIN CartAction ca ON cam.CartActionID = ca.CartActionID
WHERE ca.CartID=c.CartID AND
ca.[Action] = 'Maint'
ORDER BY cam.MaintenanceOption FOR XML PATH('')), 1, 2, '')
AS MaintenanceOption
FROM CartActionMaintenance cam
INNER JOIN CartAction ca on cam.CartActionID = ca.CartActionID AND ca.[Action] = 'Maint'
INNER JOIN Cart c on ca.CartID = c.CartID
Running the select by itself returns ~1900 rows, and takes about 600 ms.
I'm getting a sort in the middle of the execution plan, which I've attached. The estimated cost for the sort is 90%.
The table and index definitions are also attached.
CartAction has about 7500 rows; CartActionMaintenance has just under 2000.
Any suggestions you can give will be much appreciated.
- Joe
February 4, 2014 at 1:39 pm
I don't know your complete requirements, but shouldn't you be showing a lot less rows (just one per cart)? If you just want to list the Carts with Maintenance, you should avoid the inner join and try something like IN or EXISTS. If you don't care to show Carts even if a Cart doesn't have Maintenance, then you don't even need that.
The sort is explicitly defined by the order by in your query when you build your comma separated value.
The sort is not the problem, as you need it (and if you don't, just remove it).
Someone else might be able to go further with advices on DDL, but these are my 2 cents. 😉
February 4, 2014 at 2:21 pm
Luis Cazares (2/4/2014)
I don't know your complete requirements, but shouldn't you be showing a lot less rows (just one per cart)? If you just want to list the Carts with Maintenance, you should avoid the inner join and try something like IN or EXISTS. If you don't care to show Carts even if a Cart doesn't have Maintenance, then you don't even need that.
In the FROM statement in the main stored procedure, I'm joining Cart with #maintType like this:
LEFT JOIN #MaintType AS mt on c.CartID = mt.CartID
Is it possible to eliminate the inner join from my SELECT? I was thinking I needed it, but it's entirely possible I'm missing something :Whistling:
The main stored procedure pulls info on almost every cart (exceptions are ones created purely for testing purposes). I noticed there are duplicates in the above select, but a 'SELECT DISTINCT' takes care of those (leaving 1446 rows).
In the FROM statement in the main stored procedure, I'm joining Cart with #maintType like this:
LEFT JOIN #MaintType AS mt on c.CartID = mt.CartID
Is it possible to eliminate the inner join?
I'd forgotton to include this in the first post, but the Cart table has ~5100 rows.
Luis CazaresThe sort is explicitly defined by the order by in your query when you build your comma separated value.
The sort is not the problem, as you need it (and if you don't, just remove it).
Someone else might be able to go further with advices on DDL, but these are my 2 cents. 😉
Thanks for the reply.
February 4, 2014 at 2:32 pm
Another note:
I have a 2nd INSERT, which is somewhat similar:
;INSERT INTO #Grouping (CartID, GroupingNames)
SELECT c.CartID
, STUFF((SELECT '; ' + GroupName FROM CartGroups cg WHERE cg.CartID=c.CartID ORDER BY GroupName FOR XML PATH('')), 1, 2, '') AS GroupingNames
FROM dbo.Cart c
The cost % for this INSERT is 3%, and there is not a sort in the execution plan. It's biggest cost is 87% on the index seek (index on the CartGroup table, CartID and GroupName fields).
I haven't been able to determine why the first insert is so different from this one... I suppose, like Luis said, it's that join...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply