Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sort in the middle of an execution plan Expand / Collapse
Author
Message
Posted Tuesday, February 4, 2014 1:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:44 AM
Points: 35, Visits: 239
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


  Post Attachments 
TablesAndIndices.docx (2 views, 19.50 KB)
view.sqlplan (8 views, 31.91 KB)
Post #1537926
Posted Tuesday, February 4, 2014 1:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 3,559, Visits: 7,679
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1537933
Posted Tuesday, February 4, 2014 2:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:44 AM
Points: 35, Visits: 239
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

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.

[b]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.
Post #1537945
Posted Tuesday, February 4, 2014 2:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:44 AM
Points: 35, Visits: 239
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...
Post #1537949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse