January 12, 2009 at 10:42 am
Im running a select statement on a table, with no ORDER BY clause, but the execution plan is still showing a distinct sort. I cant see why this is.
query:
SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Fintan'
OR m.LastName LIKE 'Guihen'
go
Indexes:
CREATE INDEX Member1
ON dbo.member(firstname, lastname, region_no)
go
CREATE INDEX Member2
ON dbo.member(lastname, firstname, region_no)
go
Note, there is also a clustered index on member_no.
When i hover over the sort operation in the execution plan, it says its ordering by Member_no ascending.
Unless i ask it to sort, I cant see why it would. any ideas? I have a theory that its because of the OR clause, which only returns distinct rows, but I still dont know why its sorting.
January 12, 2009 at 1:05 pm
off the top of my head , an OR causes two scans of the table/index so there will have to be some sort of ordering - well that would be my guess. Have a look at io stats to see if you have two scans.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 12, 2009 at 1:15 pm
Can post the whole execution plan? Save it as .sqlplan file and zip it up.
On a guess, it's performing an aggregation to satisfy the criteria. It might be accessing the data twice to satisfy the OR clause and then doing a MERGE to get the data. If it's doing a MERGE, then it's ordering the data to satisfy the MERGE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2009 at 1:24 pm
Grant Fritchey (1/12/2009)
Can post the whole execution plan? Save it as .sqlplan file and zip it up.On a guess, it's performing an aggregation to satisfy the criteria. It might be accessing the data twice to satisfy the OR clause and then doing a MERGE to get the data. If it's doing a MERGE, then it's ordering the data to satisfy the MERGE.
You beat me to it. Was going to ask about a merge. Those have to be sorted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 1:27 pm
What SQL's probably doing is a seek on index 1 that retrieves all the rows that match on first name, then a seek on index 2 to retrieve all that match on last name. It then unions those two resultsets together.
If it didn't remove duplicates then any rows that matched on both first name and last name would appear twice in the output set. That's quite obviously wrong and hence the duplicates need to be removed. To find duplicates, the resultset needs to be sorted, hence the distint sort.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply