How should I use Hibernate Mapping while dealing with huge data table

Question

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.

Answer 1

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