Columns in order by clause which has no index

Question

Columns in order by clause which has no index

chaithu559

SSCommitted

Points: 1574
More actions
July 5, 2012 at 2:54 am

#263702

hi Everyone,
i have a query , where i use a order by clause, i have columns that has no index on it used in the order by clause. Can we say that the sort operator that is internally used takes more than 30 % of the total cost everytime..
Please help me in understanding this
Thanks and Regards
Chaithanya M

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gullimeel Hall of Fame Points: 3871 More actions · Answer 1

There is no hard and fast rule of 30% cost of the sort. It can be anything. It is relative to the overall batch and thus doesnt signify anything apart from that particular batch.

e.g. for a small table where all data is selected say 100 rows and then sorting happens on the data.It could be possible that table/clustered index scan cost is 10% and sort is 90%.

In other different scenario where you have a very big table and say 100K rows are selected (assume table scan was used) then table scan might be 90% and sort is just 10%.

This doesnt mean that the sort in second was cheaper than sort in first.In first it sorted just 100 rows and in second it sorted 100K rows thus second sort will be more costly than first sort. I am assuming that the small and big tables both have same columns and data types so that the size of the row is same and same order by columns used.

Thus it is relative. Try to avoid the sort if possible.If sort happens make sure that the minimum data is sorted in terms of rows as well as columns. Do not include the unncessary columns in order by or in select statement.

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

chaithu559 SSCommitted Points: 1574 More actions · Answer 2

Hi,

Thank you very much for quick answer. that was Awesome . One more quick question i have is , using distinct ?

As of i know , Distinct performs two opertaions :

1. Sorts the rows

2. Removes the duplicates.

How does it affect the query when used in it..

please help in understanding this

Thanks and Regards

Chaithanya M

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What exactly do you mean by 'how does it affect the query'?

p.s. Distinct doesn't always mean 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gullimeel Hall of Fame Points: 3871 More actions · Answer 4

As you mentioned already distinct will sort your output(in case it doesnt use the hash distinct) and then will remove duplicates.To sort the data you need memory and how costly the sort will be it just depends on the size of the resultset.

In case of hash distinct as well a hashing technique will be used to remove the data and even that need memory and cpu.Thus it could be very expensive opertaion based on the size of the data.

If there is an index on the columns part of the select then it might not even do a sort.

However, if you have unique index on table and run the distinct on the table then you will not see sort or hash distinct because optimizer knows there is no dup data.

Just remember one thing do not unnecessarly put the distinct in your queries.In case you are sure that the data is duplicate and you have to remove the data then only use it otherwise do not use it.

This is a nice post if you want.It describes a lot about how distinct is processed by optimizer.

http://sqlblog.com/blogs/paul_white/archive/2011/12/04/is-distinct-aggregation-still-considered-harmful.aspx

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]