使用SQL或集合对列表排序？ [英] Sort a list with SQL or as a collection?

问题描述

我有一些条目在我的数据库中有日期。什么是最好的？：

• 使用sql语句获取它们，并通过 >。


• 使用sql获取列表，并使用 collection.sort 在应用程序中排序？

感谢

解决方案

在完整集合中的值有多少。如果是，例如20-30个值，那么你可以在任何地方进行排序 - 即使相对较差的排序算法可以快速做到这一点（避免 Stooge Sort 虽然;这太可怕了） - 因为这是你可能期望在一个服务响应中获取的数据块的大小。

<但是一旦你进入更大的数据集，你需要更仔细地计划。特别是，如果你不需要，你想避免移动数据。如果数据目前只存在于数据库中，你真的不想把它全部提取到客户端，只是为了排序它（一个相对昂贵的操作），然后几乎所有的东西。实际上保存数据在数据库中排序的开始是更好的，所以，顺序拾取是微不足道的;在关系数据库术语中，保持排序的数据在功能上与在数据上维护索引相同。事实上，你可以有多个索引的数据，这可以使甚至相当复杂的查询快速。 （NoSQL DB更多样化;有些甚至不支持保持数据排序的概念。）维护索引的缺点是它们占用更多的空间，并且需要时间来维护，特别是当数据在第一个

所以...回到你的问题，你可能想尝试不排序应用程序中的数据：对于大多数数据，一个合适的索引可以更多因为它让您的代码甚至不会查看不需要的数据。但是如果你由于某些其他原因必须将它全部提取到你的应用程序中，并且你不能把它带入预排序，那么没有理由避免自己排序：Java的排序算法是高效和稳定的。但是，您应该测量是否从新订单中的数据库中提取它更快。 （问题是数据库开销是否超过重新排序的超线性成本;很多问题都在也许，很难说是答案的领域。）

另一件需要平衡的是，你的代码是否更容易做排序，而是总是将它委托给DB。保持代码更简单（更多的无错误）是一个很好的目标...

I have some entries with dates in my database. What is best?:

• Fetch them with a sql statement and also apply order by.
• Get the list with sql, and order them within the application with collection.sort or so?

Thanks

解决方案

To some extent, it depends on how many values are in the complete collection. If it is, say, 20-30 values then you can sort anywhere — even a relatively poor sorting algorithm can do that quickly (avoid Stooge Sort though; that's terrible) — as that is the sort of size of data chunk which you might expect to actually fetch in one service response.

But once you get into larger datasets you need to plan much more carefully. In particular, you want to avoid moving data around if you don't have to. If the data is currently only present in the database, you really don't want to fetch it all into the client just to sort it (a relatively expensive operation) and then throw virtually all of it away. It's far better to actually keep the data sorted in the database to start with, so that picking it up in order is trivial; in relational database terms, keeping the data sorted is functionally identical to maintaining an index on the data. Indeed, you can have multiple indices on the data, which can make even rather complex queries quick. (NoSQL DBs are more varied; some even don't support the concept of keeping data sorted.) The downside of maintaining indices is that they take up more space and they take time to maintain, particularly when the data is being created in the first place.

So… to return to your question, you probably want to try to not sort the data in the application: for most data, an appropriate index can be much more efficient as it lets your code not even look at unwanted data. But if you have to fetch it all into your application for some other reason and you can't bring it in pre-sorted, there's no reason to avoid sorting it yourself: Java's sorting algorithms are efficient and stable. But you should measure whether fetching it from the DB in the new order is faster. (The question is whether the DB overheads exceed the super-linear costs of re-sorting; lots of problems are in the domain where "maybe; hard to tell" is the answer.)

The other thing to balance is whether it is simpler for your code to not do sorting itself and instead always delegate that to the DB. Keeping your code simpler (and more bug-free) is a good goal to have…

这篇关于使用SQL或集合对列表排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！