在应用程序层对数据进行排序，还是在 order by 子句中排序更好? [英] Is it better to sort data at the application layer, or with an order by clause?

问题描述

很久以前有人建议我在应用层对数据进行排序，而不是在 SQL 中使用 ORDER BY 子句.原因是 .Net 将更有效地对 SQL 引擎进行排序.

A long time ago I was advised to sort data at the application layer and not use the ORDER BY clause in SQL. The reasoning was the .Net will sort more efficiently the SQL engine.

与此建议相冲突的是我遇到的 SSIS 最佳实践，该实践建议尽可能在 SQL 中对数据进行排序，并避免排序转换.

Conflicting with this advice is the SSIS Best Practices I've encountered that recommends sorting data in the SQL, where one can, and avoiding the Sort transformation.

SSIS 的建议对我来说很有意义.所以现在我想知道避免 ORDER BY 的最初建议是否是假的.

The SSIS advice makes sense to me. So now I am wondering if the initial advice of avoiding the ORDER BY is bogus.

给定一个不太复杂的查询，ORDER BY 是否一定意味着性能下降?

Given a not-too-complex query, does the ORDER BY necessarily mean a performance hit?

谢谢.

推荐答案

首先，如果你真的想知道一组给定的数据，那么你应该测试它.

First, if you really want to know on a given set of data, then you should test it.

也就是说，我认为您应该在服务器端排序有几个原因.

That said, there are several reasons why I think you should sort on the server side.

首先，服务器可以利用更多的硬件——多线程、多磁盘、多处理器——来进行排序.这会对性能产生很大的影响.

First, the server can take advantage of more hardware -- multiple threads, multiple disks, multiple processors -- for sorting. This can make a big difference on performance.

其次，可能不需要排序.在某些情况下，查询实际上不必对结果进行排序，因为它们已经排序.例如，可能会根据排序后的索引返回结果.

Second, the sorting may not be necessary. There may be cases where the query does not actually have to sort the results, because they are already sorted. For instance, the results may be returned based on an index that is sorted.

第三，内存使用问题和内存泄漏在客户端往往更为普遍.(好吧，你不是说你用的是java，所以你有点安全.)数据库服务器知道如何管理内存.

Third, memory usage issues and memory leaks tend to be more prevalent on the client side. (Okay, you don't say you are using java, so you are a bit safe from this.) The database server knows how to manage memory.

第四，我认为在服务器端做数据操作是个好主意.如果您尝试对每个操作进行微优化，其中一些在服务器上，一些在客户端，则编码会变得非常复杂.除非某些内容与数据的呈现特别相关，否则请在服务器上进行.

Fourth, I think it is a good idea to do the data manipulation on the server side. Coding gets quite complicated if you try to micro-optimize each operation, with some being on the server and some being on the client side. Unless something is related specifically to the presentation of the data, do it on the server.

综上所述，如果您只是为了演示目的在单个页面上对 20 个项目进行排序，那么这并没有什么区别.如果您对此感到满意，请在客户端进行.

All that said, if you are just sorting 20 items for presentation purposes on, say, a single page, then it doesn't make a difference. Do it on the client side if you are comfortable with that.

这篇关于在应用程序层对数据进行排序，还是在 order by 子句中排序更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！