Order by vs sort by

[Former Member]

If i using order by in sql to retrieve data into internal table and retrieve data into internal table then use sort by. The performance different is big or not? or the difference is little. Thanks!

[Former Member]

If i using order by in sql to retrieve data into internal table and retrieve data into internal table then use sort by. The performance different is big or not? or the difference is little. Thanks!

[Former Member]

The argument is based on scalability not on time, there is only 1 database but many application servers, so move load away from the database to the application servers, even by accepting a small overhead!

Use 'order by', if the database does not have to do anything, for example if the access is by primary key, then the records will anyway come in the order by primary key. The keyword just shows that the result is ordered and no sort is necessary anymore.

In any other case, where used index and wanted sort order are different, you should use the SORT command.

Exceptions are very large result sets, it might be that the can not be handled by the application server but by the database. Also UP TO n ROWS together with a sort order, i.e. the Top n contributions should be done on the database, if the result set is much larger than n. But you should be aware that this is an expensive statement.

Siegfired

[Former Member]

hi portfolio,

It is better to use sort by after the select query.

select query + order by clause -> application server -> database server

sort by clause -> application server.

you can note the difference.

[Former Member]

HI

Orderby clause sorts the entries in database where as sort by shall retrieve the entries into ABAP memory and performs the sorting there. each has its own adv and disadv.

Orderby-

Adv :can be used to sort large number of records as we have a memory size restriction for ABAP memory

Dis Adv: Puts load on database server

Sortby: Puts load on application server but becomes slow in case of large number of records in internal table.

[Former Member]

Hi,

Apart from the performance of select query Order by will also effect the data in internal table :

Order by :

Eg.

1. say ur internal table can hold 10 records and while select u fetch 11 records then order by will only allow to hold 10 records and the 11th record would be deleted.

2. It will search database on the ordered field and the Data will be orderd by the oder key

Sort by :

Eg.

1.say ur internal table can hold 10 records and while select u fetch 11 records then sort by will allow all the records to come in ur internal table and then sort them with the key you want.

2. It will fetch the data in ur ninternal table and then will sort that.

PS: Reward Points if helpfull.

Regards

Naveen Gupta

[Former Member]

Hi Portfolio,

The ORDER BY clause is executed on the database server while the ABAP SORT statement is executed on the application server. The database server will usually be the bottleneck, so sometimes it is better to move the sort from the database server to the application server.

If you are not sorting by the primary key ( E.g. using the ORDER BY PRIMARY key statement) but are sorting by another key, it could be better to use the ABAP SORT statement to sort the data in an internal table. Note however that for very large result sets it might not be a feasible solution and you would want to let the database server sort it.