Most of the times, when it comes to sort some data, we have two options:
- Sort on the SQL server -- use ORDER BY clause
- Sort on client one we get the data from the database
When would you use one over the other and why?
views:
119answers:
3
+15
A:
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.
alex
2010-10-27 02:55:54
+1: It's part of the databases' job, data retrieval.
OMG Ponies
2010-10-27 02:57:55
+1 for correctly identifying SQL as an abstraction layer.
Bill Karwin
2010-10-27 02:58:00
+1 just for fun ;-P
zerkms
2010-10-27 02:59:36
Wow, an upvote from you guys! Thank! :)
alex
2010-10-27 02:59:49
+1 because I hate feeling left out!
Andrew Barber
2010-10-27 03:01:39
Life would be much simpler if more dev's realized that a SQL Servers job is 1 part data storage, 1 part enforcer, and 1 part data manipulation.
Chris Lively
2010-10-27 04:19:33
The article about Digg achieving incredible performance gains by using Cassandra probably isn't the best example, both because it's not about SQL, and because using Cassandra inexpertly seems to have led to Digg's current death spiral.
Bill Karwin
2010-10-27 14:05:32
@Bill Karwin Yeah you are right. I might actually remove it, as the article headline is designed to be attention grabbing but it doesn't accurately describe the entire article.
alex
2010-10-27 14:28:30
+13
A:
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.
Bill Karwin
2010-10-27 02:57:31
+2
A:
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.
Sam Saffron
2010-10-27 03:22:24