8

I have around 1000 rows of data.On the ASPX page, whenever the user clicks the sort button, it will basically sort the result according to a specific column.

I propose to sort the result in the SQL query which is much more easier with just an Order by clause.

However, my manager insisted me to store the result in an array, then sort the data within an array because he thinks that it will affect the performance to call the database everytime the user clicks the sort button.

Just out of curiosity - Does it really matter?

Also, if we disregard the number of rows, performance wise, which of these methods is actually more efficient?

Improve this question
16
  • 4
    Ask your manager: what if there are a million rows? And don't leave us hanging. Commented Aug 19, 2014 at 20:37
  • 4
    For 1000 rows, it really doesn't matter - performance wise. However, SQL Server is pretty damn efficient in doing stuff, including sorting. So, think about it. Query, then sort in memory then return to client or query AND sort and return to client? Commented Aug 19, 2014 at 20:39
  • 1
    for pagination and sorting I use LINQ (in memory) just because it's so simple. Commented Aug 19, 2014 at 20:40
  • 2
    Your manager is right. The problem is not sorting but moving the rows over the network each time. So, get it sorted from the DB if you will but re-sort according to user clicks at the client. Commented Aug 19, 2014 at 20:44
  • 2
    @MarcelN. Keep in mind that to get the DB to sort the data for you you need to send the entire result set across the network to your application. Do you think that it's sorting algorithm is so much faster than sorting an in-memory collection as to make up for all of that network time? Commented Aug 19, 2014 at 20:44

4 Answers 4

Reset to default
17

Well, there are three options:

  1. Sort in the SQL
  2. Sort server-side, in your ASP code
  3. Sort client-side, in your Javascript

There's little reason to go with (2), I'd say. It's meat and drink to a database to sort as it returns data: that's what a database is designed to do.

But there's a strong case for (3) if you want to have a button that the user can click. This means it's all done client-side, so you have no need to send anything to the web server. If you have only a few rows (and 1000 is really very few these days), it'll feel much faster, because you won't have to wait for sending the request and getting a response.

Realistically, if you've got so many things that Javascript is too slow as a sorting mechanism, you've got too many things to display them all anyway.

In short, if this is a one-off thing for displaying the initial page, and you don't want the user to have to interact with the page and sort on different columns etc., then go with (1). But if the user is going to want to sort things after the page has loaded, then (3) is your friend.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Don't neglect scalability, #1 will potentially cause you to re-write your app later, #2 is generally easy to scale for websites, and #3 is completely scaled as long as your pushing all data to the client.
@jtimperley #3 might scale clients, but it doesn't scale data.
#2 is very valid, a lot of times #1 isn't an option due to stored procedures etc not being coded in a way to allow for custom sorts. #3 is ideal but requires an architecture behind it to render lists etc, a lot of websites simply render grids as tables and don't have the sorting available to them without much redesign.
#2 (and #3) might scale for small datasets, but it doesn't scale for large datasets
Its actually not "meat and drink" for databases to sort. It's a very resource intensive operation. Often leading to tempdb spills. This was what I was trying to get at with my answer below when someone came in and detailed the conversation. I'm not saying you shouldn't use order by.... Just be selective. See the links in my answer.
|
3

Short Answer

Ah... screw it: there's no short answer to a question like this.

Longer Answer

The best solution depends on a lot of factors. The question is somewhat vague, but for the sake of simplicity let's assume that the 1000 rows are stored in the database and are being retrieved by the client. Now, a few things to get out of the way:

  1. Performance can mean a variety of things in a variety of situations.
  2. Sorting is (relatively) expensive, no matter where you do it.
  3. Sorting is least expensive when done in the database, as the database already has the all the necessary data and is optimized for these operations.
  4. Posting a question on SO to "prove your manager wrong" is a bad idea. (The question could easily have been asked without mentioning the manager.)

Your manager believes that you should upload all the data to the client and do all the processing there. This idea has some merit. With a reasonably sized dataset processing on the client will almost always be faster than making a round trip to the server. Here's the caveat: you have to get all of that data to the client first, and that can be a very expensive operation. 1000 rows is already a big payload to send to a client. If your data set grows much larger then you would be crazy to send all of it at once, particularly if the user really only needs a few rows. In that case you'll have to do some form of paging on the server side, sending chunks of data as the user requests it, usually 10 or 20 rows at a time. Once you start paging at the server your sorting decision is made for you: you have no choice but to do your sorting there. How else would you know which rows to send?

For most "line-of-business" apps your query processing belongs in the database. My generalized recommendation: by all means do your sorting and paging in the database, then return the requested data to the client as a JSON object. Please don't regenerate the entire web page just to update the data in the grid. (I've made this mistake and it's embarrassing.) There are several JavaScript libraries dedicated solely to rendering grids from AJAX data. If this method is executed properly your page will be incredibly responsive and your database will do what it does best.

Improve this answer

Comments

2

We had a problem similar to this at my last employer. we had to return large sets of data efficiently, quickly and consistently into a datagridview object.

The solution that they came up was to have a set of filters the user could use to narrow down the query return and to set the maximum number of rows returned at 500. Sorting was then done by the program on an array of those objects.

The reasons behind this were:

  1. Most people will not not process that many rows, they are usually looking for a specific item (Hence the filters)
  2. Sorting on the client side did save the server a bunch of time, especially when there was the potential for thousands of people to be querying the data at the same time.
  3. Performance of the GUI object itself started to become an issue at some point (reason for limiting the returns)

I hope that helps you a bit.

Improve this answer

Comments

0

From both a data-modeling perspective and from an application architecture pattern, its "best practice" to put sorting/filtering into the "controller" portion of the MVC pattern. That is directly opposed to the above answer several have already voted for.

The answer to the question is really: "It depends"

If the application stays only one table, no joins, and a low number of rows, then sorting in JavaScript on the client is likely going to win performance tests.

However, since it's already APSX, you may be preparing for your data/model to expand.--Once there are more tables and joins, and if the UI includes a data grid where the choice of which column to sort will change on a per-client basis, then maybe the middle-tier should be handling this sorting for your application.

I suggest reviewing Tom Dykstra's classic Contosa University ASP.NET example which has been updated with Entity Framework and MVC 5. It includes a section on Sorting, Filtering and Paging. This example shows the value of proper MVC architecture and the ease of implementing sorting/filtering on multiple columns.

Remember, applications change (read: "grow") over time so plan for it using an architecture pattern such as MVC.

Improve this answer

1 Comment

This is not in disagreement with the other answers. Since L2EF is [presumably] used in the example then simply saying "Controller" does not answer the question of database vs. web app (vs. web client).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.