在处理大量数据表时，我应该如何使用Hibernate映射 [英] How should I use Hibernate Mapping while dealing with huge data table

问题描述

问题定义：

我有一个数据库表，其中包含大量数据（超过100,000行），表结构就像

  AppID DocID DocStatus 
 1 100 0 
 1 101 1 
 2 200 0 
 2 300 1 
  

每个applicationID可能有成千上万的文档，我必须获取状态为0的文档的Count和文档的数量状态1按applicationID分组。

当我使用hibernate映射这个对象时，由于大量的表数据，它会占用很多堆内存。

如何使用Hibernate查询来实现这一点？
OR我应该使用SQL Query还是Stored Procedure？

注意：我的Web应用程序是在JAVA / Tapestry框架中使用Hibernate 3.数据库是SQL Server 2012.

解决方案

每次你有一个dat以Java为中心的问题（与以Java领域模型为中心的问题相反），您应该直接使用SQL。你的数据库将比你的Java代码快得多，因为计算可以靠近数据来执行，而不是通过网络将所有数据转移到你的内存中。另请参阅2 。处理内存中的数据。

您可以直接或通过本机查询或任何第三方您选择的SQL库，例如 MyBatis 或 jOOQ 。

您可以通过以下任何查询解决您的问题：

使用GROUP BY

$ b

  SELECT [AppID]，[DocStatus]，count （*）
 FROM [MyTable] 
 GROUP BY [AppID]，[DocStatus] 
  

SQLFiddle 上的示例

< h3>使用嵌套选择
$ b

  SELECT [AppID]，
（SELECT count（* ）FROM [应用程序] 
 AND [DocStatus] = 0）[Status_0]，
（SELECT count（* ）从[MyTable] [t2] 
 WHERE [t1]。[AppID] = [t2]。[AppID] 
 AND [DocStatus] = 1）[Status_1] 
 FROM [MyTable] [t1] 
 GROUP BY [APPID] 
  

SQLFiddle

使用SUM（）

$ b $ {
$ b $ prefb =lang-sql prettyprint-override> SELECT [AppID]，
SUM（IIF（[DocStatus] = 0，1，0））[ Status_0]，
SUM [IIF（[DocStatus] = 1,1,0））[Status_1]
FROM [MyTable] [t1]
GROUP BY [AppID]


SQLFiddle

使用PIVOT

  SELECT [AppID]，[0]，[1] 
 FROM（
 SELECT [AppID]，[DocStatus] 
 FROM [MyTable ] [
] [t] 
 PIVOT（count（[DocStatus]）FOR [DocStatus] IN（[0]，[1]））[pvt] 
  

SQLFiddle

a>

Problem Definition:
I have a Database table with huge amount of data(more than 100,000 rows) , table structure is like

AppID  DocID  DocStatus 
1      100    0
1      101    1    
2      200    0    
2      300    1

Per applicationID there may be thousands of Documents, I have to fetch Count of the documents with status 0 and count of the documents with status 1 grouped by applicationID.

When I am mapping this object using hibernate, it will eat up lot of heap memory due to large amount of table data.

How Can I achieve this using Hibernate query? OR Should I use SQL Query or Stored Procedure for this ?

Note : My Web Application is in JAVA/Tapestry framework and using Hibernate 3. Database is SQL Server 2012.

解决方案

Every time you have a data-centric problem (as opposed to a Java domain-model-centric one), you should use SQL directly. Your database will be much faster than your Java code, because calculations can be performed closely to the data, instead of transferring all of it through the wire and into your memory. See also "2. Processing Data in Memory" of this blog post.

You can achieve this with JDBC directly, or with a native query, or with any third-party SQL library of your choice, such as MyBatis or jOOQ.

Your problem can be trivially solved with any of these queries:

Using GROUP BY

SELECT [AppID], [DocStatus], count(*)
FROM [MyTable]
GROUP BY [AppID], [DocStatus]

Example on SQLFiddle

Using nested selects

SELECT [AppID],
       (SELECT count(*) FROM [MyTable] [t2]
        WHERE [t1].[AppID] = [t2].[AppID]
        AND [DocStatus] = 0) [Status_0],
       (SELECT count(*) FROM [MyTable] [t2]
        WHERE [t1].[AppID] = [t2].[AppID]
        AND [DocStatus] = 1) [Status_1]
FROM [MyTable] [t1]
GROUP BY [AppID]

Example on SQLFiddle

Using SUM()

SELECT [AppID],
       SUM(IIF([DocStatus] = 0, 1, 0)) [Status_0],
       SUM(IIF([DocStatus] = 1, 1, 0)) [Status_1]
FROM [MyTable] [t1]
GROUP BY [AppID]

Example on SQLFiddle

Using PIVOT

SELECT [AppID], [0], [1]
FROM (
  SELECT [AppID], [DocStatus]
  FROM [MyTable]
) [t]
PIVOT (count([DocStatus]) FOR [DocStatus] IN ([0], [1])) [pvt]

Example on SQLFiddle

这篇关于在处理大量数据表时，我应该如何使用Hibernate映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！