如何处理来自数据库的巨大结果集 [英] How to handle huge result sets from database

问题描述

我正在设计一个多层数据库驱动的Web应用程序 - SQL关系数据库，用于中间服务层的Java，用于UI的Web。该语言并不重要。

I'm designing a multi-tiered database driven web application – SQL relational database, Java for the middle service tier, web for the UI. The language doesn't really matter.

中间服务层执行数据库的实际查询。用户界面只是询问某些数据，并且没有数据库支持的概念。

The middle service tier performs the actual querying of the database. The UI simply asks for certain data and has no concept that it's backed by a database.

问题是如何处理大型数据集？用户界面要求提供数据，但结果可能很大，可能太大而无法容纳在内存中。例如，街道标志应用程序可能具有以下服务层：

The question is how to handle large data sets? The UI asks for data but the results might be huge, possibly too big to fit in memory. For example, a street sign application might have a service layer of:

StreetSign getStreetSign(int identifier)
Collection<StreetSign> getStreetSigns(Street street)
Collection<StreetSign> getStreetSigns(LatLonBox box)

UI图层要求获得符合某些条件的所有街道标志。根据标准，结果集可能很大。 UI层可能会将结果划分为单独的页面（对于浏览器）或仅将它们全部呈现（为Goolge Earth提供服务）。可能很大的结果集可能是性能和资源问题（内存不足）。

The UI layer asks to get all street signs meeting some criteria. Depending on the criteria, the result set might be huge. The UI layer might divide the results into separate pages (for a browser) or just present them all (serving up to Goolge Earth). The potentially huge result set could be a performance and resource problem (out of memory).

一种解决方案不是返回完全加载的对象（StreetSign对象）。而是返回某种延迟加载每个单独对象的结果集或迭代器。

One solution is not to return fully loaded objects (StreetSign objects). Rather return some sort of result set or iterator that lazily loads each individual object.

另一种解决方案是更改服务API以返回所请求数据的子集：

Another solution is to change the service API to return a subset of the requested data:

Collection<StreetSign> getStreetSigns(LatLonBox box, int pageNumber, int resultsPerPage)

当然UI仍然可以请求巨大的结果设置：

Of course the UI can still request a huge result set:

getStreetSigns(box, 1, 1000000000)

我很好奇这个场景的标准行业设计模式是什么？

I'm curious what is the standard industry design pattern for this scenario?

推荐答案

第一个问题应该是：

¿用户需要或有能力管理这一数据量吗？

¿The user needs to, or is capable of, manage this amount of data?

虽然结果集应该被分页，但如果它的潜在大小如此巨大，答案将可能不会，因此UI不应该尝试显示它。

Although the result set should be paged, if its potentially size is so huge, the answer will be "probably not", so the UI shouldn't try to show it.

我参与了医疗保健系统的J2EE项目，处理大量的存储数据，数百万患者，访问，表格等，一般规则不超过100或用于任何用户搜索的200行，建议用户这些标准集可以产生更多信息理解。

I worked on J2EE projects on Health Care Systems, that deal with enormous amount of stored data, literally millions of patients, visits, forms, etc, and the general rule is not to show more than 100 or 200 rows for any user search, advising the user that those set of criteria produces more information that he can understand.

实现这一点的方法因项目而异，可以强制UI在启动之前向服务层询问查询的大小，或者，如果结果集增长太多，则可以从服务层抛出异常（但是这种方式将服务层与UI的有限实现相结合）。

The way to implement this varies from one project to another, it is possible to force the UI to ask the service tier the size of a query before launching it, or it is possible to throw an Exception from the service tier if the result set grows too much (however this way couples the service tier with the limited implementation of an UI).

小心！这并不意味着服务层上的每个方法如果其结果大小超过100就必须抛出异常，这个一般规则仅适用于直接向用户显示的结果集，这是将控件放在UI中的更好理由而是在服务层上。

Be careful! This not means that every method on the service tier must throw an Exception if its result sizes more than 100, this general rule only applies to result sets that are shown to the user directly, that is a better reason to place the control in the UI instead on the service tier.

这篇关于如何处理来自数据库的巨大结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！