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 123»»»

ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ? Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 2:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
Hi All,

When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer?

1) Bubble Sort
2) Quick Sort
3) Merge Sort
4) Heap Sort
5) Insertion Sort

Inputs are welcome!



karthik
Post #1427851
Posted Thursday, March 7, 2013 2:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Well the optimiser won't use any algorithm. The optimiser optimises queries, it doesn't run them.

Why does it matter?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1427853
Posted Thursday, March 7, 2013 5:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
I have a table with 5 rows.

100
5
232
534453
23

No index has been created.

If I use ORDER BY , which alogirthm is used to do this sort operation?

5
23
100
232
534453


karthik
Post #1427915
Posted Thursday, March 7, 2013 5:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:06 PM
Points: 20,705, Visits: 32,355
karthik M (3/7/2013)
I have a table with 5 rows.

100
5
232
534453
23

No index has been created.

If I use ORDER BY , which alogirthm is used to do this sort operation?

5
23
100
232
534453


Again, why? It does it and it does it quickly.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427923
Posted Thursday, March 7, 2013 5:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
The query engine will do what it does. That's in the internals. No way to know without a debugger or insider knowledge. It will use tempdb though.

----------------------------------------------------
"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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1427924
Posted Thursday, March 7, 2013 9:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Grant Fritchey (3/7/2013)
The query engine will do what it does. That's in the internals. No way to know without a debugger or insider knowledge. It will use tempdb though.


Unless the data's small enough it can manage entirely within the memory grant. Not much chance of that except with trivial data.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1428059
Posted Thursday, March 7, 2013 9:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
GilaMonster (3/7/2013)

Unless the data's small enough it can manage entirely within the memory grant. Not much chance of that except with trivial data.


Not arguing, just clarifying, won't it allocate some space on tempdb anyway?


----------------------------------------------------
"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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1428063
Posted Thursday, March 7, 2013 9:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Grant Fritchey (3/7/2013)
GilaMonster (3/7/2013)

Unless the data's small enough it can manage entirely within the memory grant. Not much chance of that except with trivial data.


Not arguing, just clarifying, won't it allocate some space on tempdb anyway?


Sorts I don't think so, not unless it actually spills. Until it spills, it's within the query execution's memory memory space.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1428073
Posted Friday, March 8, 2013 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
whereever the optimizer sort the data, it sould use some algorothim to do this operation? Right?

My question is simple, is there any alogrithm used for ORDER BY clause or not by the optimizer?




karthik
Post #1428446
Posted Friday, March 8, 2013 2:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:31 AM
Points: 1,191, Visits: 9,882
The optimiser is the wrong term for this, it's not the query optimiser that performs sorts.

The Query Engine obviously uses algorithms to perform sorts. Those algorithms are proprietary and undocumented, but you'd imagine them to be based on some of the better performing sorting mechanisms above, tweaked and changed for SQL's internal storage characteristics, index usage, parallelism, statistics, etc.
Post #1428461
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse