4

In terms of the memory and CPU usage, which one is better, sorting a resultset in SQL Server or in .NET?

For example, I have a stored procedure called csp_Products that returns 1000 rows - the result should be sorted by product name, is it better to sort this in SQL Server using an ORDER BY clause or is it better to do this in .NET after the data has been retrieved?

Improve this question
3
  • 2
    What they (first three replies) said. With only 100 rows to sort (and something faster than a 386), it probably won't make much difference. Commented Sep 6, 2011 at 13:39
  • ok but that's not the question. imagine there are more rows. Commented Sep 6, 2011 at 13:44
  • 1
    @William no offense but if that's not the question then maybe you should change the question. If the 100 rows are not relevant, why mention them? Commented Sep 6, 2011 at 13:54

5 Answers 5

Reset to default
4

If you can do it in SQL Server with an ORDER BY clause, then do it. SQL Server is made for retrieving and manipulating data and will be faster.

That being said, depending on the type of data being returned and the number of rows there may not be a noticeable difference. 100 rows really isn't that much data to have to worry about performance.

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

2 Comments

we have a SQL Server MVP in our office which says do the sorting in .NET and that's better - SQL Server is more costly to sort!
Which SQL Server MVP is that?
4

I would go for SQL server since it can use indexes (if there are any it can take advantage of)

Improve this answer

Comments

4

I'll add that if you want to know whether (a) or (b) is better, test (a) and (b) against each other using your data, your hardware, and your usage patterns. While I agree in this case you're not likely to uncover much difference, there are very few hard and fast rules - always some "it depends" factors that can change the answer from (a) to (b) or vice-versa. For example, if the column you want to sort by is not indexed, there are 80 billion rows, the 100 rows you want are not identified by this ordering, and your .NET machine has 10x more RAM than SQL Server, I'd likely sort on the client.

Improve this answer

Comments

2

situation dependant, but ideally you want to do the sorting on the database, and limit the amount of data returned to only the specified query. Don't return more than you need, and return it in the format that gives you the least amount of work to process it.

Improve this answer

2 Comments

yeah thanks but the question wasn't about the number of rows returned and filtering.
your question is about sorting on server side or application side. My anwser does not entail about the amount of rows returned.
0

As said by others, it's good practice to sort the data in database itself and you can group by different criteria and for each group you can sort also. It uses indexes as well which could be consider one of the performance benefit provided you have well indexed database.

Having said that, one should not return all the rows and should use Custom Paging along with necessary filter criteria, which returns no of rows that you've chosen for paging and Total No of results found. It will definitely boost performance.

Improve this answer

Comments

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.