Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plan with Sort


Execution Plan with Sort

Author
Message
ken_cox
ken_cox
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 260
I am playing around with and continuing to learn SQL. I am using the AdventureWorks2012 database with the following query to get a list of people's first and last name and job title, with their job title = Sales Representative and ordering by Last Name:

SELECT p.FirstName
,p.LastName
,e.JobTitle
FROM AdventureWorks2012.Person.Person p
JOIN AdventureWorks2012.HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Sales Representative'
ORDER BY p.LastName

I also created an index on each table which my query uses:

USE [AdventureWorks2012]
GO

/****** Object: Index [IX_JobTitle_BusinessEntityID] Script Date: 3/21/2013 8:48:01 PM ******/
CREATE NONCLUSTERED INDEX [IX_JobTitle_BusinessEntityID] ON [HumanResources].[Employee]
(
[JobTitle] ASC,
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--------------------------------
USE [AdventureWorks2012]
GO

/****** Object: Index [IX_LastName_BusinessEntityID_IN_FirstName] Script Date: 3/21/2013 8:48:52 PM ******/
CREATE NONCLUSTERED INDEX [IX_LastName_BusinessEntityID_IN_FirstName] ON [Person].[Person]
(
[BusinessEntityID] ASC,
[LastName] ASC
)
INCLUDE ( [FirstName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

If I run this query without the order by clause or with a column in the Employee table instead of p.LastName, I do not get a sort operator in the execution plan. When ordering by last name, I get a sort operator between the Nested Loops (Inner Join) and Select in the Execution Plan.

The question becomes would there be a way to remove the sort operator and do the sorting in one of the indexes before the Nested Loops operator or by changing my query?

Should I not worry about and this would be the best it could get?

I am not fully sure what I am looking for within the execution plan so any help would be appreciated.

Thank you.
Sam S Kolli
Sam S Kolli
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 3486
Ken,

Since BusinessEntityId is the Clustered Key, the data is sorted in the table is physically stored in its order. And since BusinessEntityId is being used in the join condition, it is expected that the optimizer would use that Clustered key to get the data and then later sort it by the lastname.

Now if you force the query to use the last name index (which is IX_Person_LastName_FirstName_MiddleName in this case), you would expect the results to be read in order of the index (query below).


SELECT p.FirstName ,p.LastName ,e.JobTitle
FROM AdventureWorks2012.Person.Person p WITH (INDEX (IX_Person_LastName_FirstName_MiddleName))
INNER JOIN AdventureWorks2012.HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Sales Representative'
ORDER BY p.LastName



However there is still a sort operator in the plan. This may be because the optimizer decided that going thru the index in an optimal way, and not necessarily sorting the output might be beneficial in the overall query. So it grabs the data as it can and then it sorts in the end to satisfy the order by clause. If you select the properties of the NonClustered Index Scan operator for the above query, you will see that the output is not ordered.

However, if you use Fast 1 option as below, then the scan of the non-clustered index is sorted (look at the properties of the operator; Ordered is set to true.) And thus a sort is avoided in the later stages.


SELECT p.FirstName ,p.LastName ,e.JobTitle
FROM AdventureWorks2012.Person.Person p WITH (INDEX (IX_Person_LastName_FirstName_MiddleName))
INNER JOIN AdventureWorks2012.HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Sales Representative'
ORDER BY p.LastName
OPTION (FAST 1)



Instead of me parroting what the experts say, please read here and here to see the reasons why.

http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/22/did-you-know-forcing-a-nonclustered-index-scan-to-avoid-sorting.aspx

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/09a6060a-1f72-4438-a3b2-209c240ee4d6/

So you can get to avoid the sort operator, but it has its costs and one main advantage (data streaming can begin early; as written in the second link).
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17585 Visits: 32262
I'd have to test it to be sure, but you might be able to create an index with LastName as the leading edge and the other columns as part of the INCLUDE clause. Although, you don't need to add the clustered key to any non-clustered index because it's already going to be there as part of the structure as provided by SQL Server.

I'd be hard pressed to say more without seeing the execution plan myself.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ken_cox
ken_cox
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 260
Thanks you Sam. That answered my question and gave me something to look at and test.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search