SQL Sorting: server vs. client

Most of the times, when it comes to开发者_开发百科 sort some data, we have two options:

1. Sort on the SQL server -- use ORDER BY clause
2. Sort on client one we get the data from the database

When would you use one over the other and why?

---

Always sort where possible in the SQL server.

The SQL abstracts away the steps of querying the database, sorting, etc. Use as much abstraction as you can - why would you select a bunch of rows, and then write a sorting algorithm when you can do it in the SQL with ORDER BY.

See also Bill Karwin's answer.

---

I would use sorting on the server when the dataset is large and I know there's an index in the database that will help with the sort. If there is no index, I would create one.

I would use sorting on the client only if the dataset is small enough to fit comfortably in application memory and the application system has an efficient sorting function, and there is no index in the database. But if the dataset is that small, the cost of sorting in the server even with no index is probably easy to bear.

---

In general you want to sort in the database, its simpler, involves less code and will be more efficient.

However, there are some edge conditions where client based sorting makes sense.

One dataset, multiple sorts, dataset in memory

Say you have a javascript gui that looks at a table with 500 rows, if you would like to give your users the flexibility to sort by whatever column they want it may make sense to do the sorting in javascript so you cut down on a trip to the server.

The main advantage for using client side sorting is to cut down on network traffic and db use (when offering multiple sorts). You do have to be very careful with your sorting performance and memory constraints. Large datasets, client side, are often virtualized so not all the data actually arrives at the client.