Sorting inside the database or Sorting in code behind? Which is best?

Question

I have a dropdown list in my aspx page. Dropdown list's datasource is a datatable. Backend is MySQL and records get to the datatable by using a stored procedure.

I want to display records in the dropdown menu in ascending order.

I can achieve this by two ways.

1) dt is datatable and I am using dataview to filter records.

dt = objTest_BLL.Get_Names();

dataView = dt.DefaultView; 
dataView.Sort = "name ASC";
dt = dataView.ToTable();

ddown.DataSource = dt;
ddown.DataTextField = dt.Columns[1].ToString();
ddown.DataValueField = dt.Columns[0].ToString();
ddown.DataBind();

2) Or in the select query I can simply say that

SELECT
`id`,
`name`
FROM `test`.`type_names`
ORDER BY `name` ASC ;

If I use 2nd method I can simply eliminate the dataview part. Assume this type_names table has 50 records. And my page is view by 100,000 users at a minute. What is the best method by considering efficiency,Memory handling? Get unsorted records to datatable and filter in code behind or sort them inside the datatabse?

Answer 1

Note - Only real performance tests can tell you real numbers.. Theoretical options are below (which is why I use word guess a lot in this answer). You have at least 3 (instead of 2) options -

1. Sort in database - If the column being sorted on is indexed.. Then this may make most sense, because overhead of sorting on your database server may be negligible. SQL servers own data caches may make this super fast operation.. but 100k queries per minute.. measure if SQL gives noticeably faster results without sort.

2. Sort in code behind / middle layer - Likely you won't have your own equivalent of index.. you'd be sorting list of 50 records, 100k times per minutes.. would be slower than SQL, I would guess. Big benefit would apply, only if data is relatively static, or very slow changing, and sorted values can be cached in memory for few seconds to minutes or hours..

3. The option not in your list - send the data unsorted all the way to the client, and sort it on client side using javascript. This solution may scale the most... sorting 50 records in Browser, should not be a noticeable impact on your UX.