Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: Scrollable Results Out of Memory error
PostPosted: Thu Oct 30, 2008 4:50 am 
Newbie

Joined: Thu Oct 30, 2008 3:26 am
Posts: 10
Hi I am trying to read all the rows from MS SQL Server.
There are 70000 rows in a table.
Blob field size in each row is 33KB.
When read all records with the JDBC program, it took 2-3 minutes to read all the records.
But when tried to with hibernate, its throwing Outof memory Exception.
I used JDBC 2 driver provided by Microsoft.

But in practical, when used Scrollable Results with Hibernate, It shouldn't pool the objects in
memory and it shouldn't eat memory. Why it is happening like this for MS SQL Server 2005

When run the same program for Oracle 10G, it just ran in 11 Sec, without any memory occupancy.

Hibernate version: 3.2
Mapping documents:
SlipMnt.hbm.xml
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="SlipMnt" table="SLIP_MNT" >
      <composite-id>
         <key-property name="factsPin" column="FACTS_PIN"/>
         <key-property name="digitNo" column="DIGIT_NMBR" />
      </composite-id>
        <property name="rows"  column="MNT_ROWS"/>
        <property name="columns"  column="MNT_COLUMNS"/>
        <property name="gender"  column="GENDER"/>
        <property name="agencyID" column="CONTROL_AGENCY_ID"/>
        <property name="factsPinDigit" column="FACTS_PIN_DIGIT"/>
        <property name="compressionFormat" column="COMPRESSION_FORMAT"/>
        <property name="mnt" column="DIGIT_MNT" />
        <property name="mntSize" column="MNT_SIZE"/>
      </class>
   </hibernate-mapping>

applicationContext.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:p="http://www.springframework.org/schema/p"
   xmlns:aop="http://www.springframework.org/schema/aop"
   xmlns:context="http://www.springframework.org/schema/context"
   xmlns:jee="http://www.springframework.org/schema/jee"
   xmlns:tx="http://www.springframework.org/schema/tx"
   xsi:schemaLocation="
         http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
         http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
         http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
         http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd
         http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">


   <context:property-placeholder location="classpath:jdbc.properties" />

   <bean id="sqlsrvDataSource" class="org.apache.commons.dbcp.BasicDataSource"            
               destroy-method="close" p:driverClassName="${jdbc.driverClassName}" p:url="${jdbc.url}" />

      <bean id="sqlsrvSessionFactory"
            class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
         <property name="dataSource" ref="sqlsrvDataSource"/>
         <property name="mappingResources">
            <list>
               <value>SlipMnt.hbm.xml</value>
            </list>
         </property>
         <property name="hibernateProperties">
            <props>
               <prop key="hibernate.dialect">${hibernate.dialect}</prop>
               <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
               <prop key="hibernate.generate_statistics">${hibernate.generate_statistics}</prop>
               <prop key="hibernate.jdbc.use_scrollable_resultsets">${hibernate.jdbc.use_scrollable_resultsets}</prop>
            </props>
         </property>
      </bean>
   <bean id="dbread" class="DBRead">
      <property name="sessionFactory">
         <ref bean="derbySessionFactory" />
      </property>

   </bean>
</beans>

jdbc.properties
Code:
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://CMC-8N1K8VZK5KC:1433;databaseName=factstest;selectMethod=cursor;user=sa;password=sa;
jdbc.username=sa
jdbc.password=sa
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.show_sql=false
hibernate.generate_statistics=false
hibernate.connection.pool_size=3
hibernate.jdbc.use_scrollable_resultsets=true

Code between sessionFactory.openSession() and session.close():
Code:
   import java.io.IOException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

/**
* @author sridhar
*
*/
public class DBRead extends HibernateDaoSupport {

   private SessionFactory sessionFactory = null;

   @SuppressWarnings("unchecked")
   public void readOrclDB() {

      
      Timestamp start = new Timestamp(Calendar.getInstance()
            .getTimeInMillis());


      getHibernateTemplate().execute(new HibernateCallback(){
         @Override
         public Object doInHibernate(Session session)
               throws HibernateException, SQLException {
            SlipMnt slip = null;
            ScrollableResults results = session.createQuery("from SlipMnt").scroll(ScrollMode.SCROLL_INSENSITIVE);
            while(results.next()){
               slip = (SlipMnt)results.get(0);
            }
            return null;
         }

      });
      Timestamp end = new Timestamp(Calendar.getInstance().getTimeInMillis());
      System.out.println(start);
      System.out.println(end);
      System.out.println(end.getTime() - start.getTime());

   }

   public static void main(String[] args) throws IOException {
      final ApplicationContext ctx = AppContext.getContext();
      final DBRead dbread = (DBRead) ctx.getBean("dbread");

      dbread.readOrclDB();
   }

}


Full stack trace of any exception that occurs:
Code:
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext).
log4j:WARN Please initialize the log4j system properly.
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
        at com.microsoft.sqlserver.jdbc.PLPInputStream.getBytes(Unknown Source)
        at com.microsoft.sqlserver.jdbc.DDC.binaryPLPToObject(Unknown Source)
        at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(Unknown Source)
        at com.microsoft.sqlserver.jdbc.DTV.getValue(Unknown Source)
        at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
        at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getBlob(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getBlob(Unknown Source)
        at org.apache.commons.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:527)
        at org.hibernate.type.BlobType.get(BlobType.java:57)
        at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:111)
        at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
        at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)
        at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
        at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)
        at org.hibernate.loader.Loader.getRow(Loader.java:1206)
        at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
        at org.hibernate.loader.Loader.loadSingleRow(Loader.java:268)
        at org.hibernate.impl.ScrollableResultsImpl.prepareCurrentRow(ScrollableResultsImpl.java:231)
        at org.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:100)
        at DBRead$1.doInHibernate(DBRead.java:42)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
        at DBRead.readOrclDB(DBRead.java:33)
        at DBRead.main(DBRead.java:62)

Name and version of the database you are using: MS SQL Server 2005


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 30, 2008 6:29 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Hibernate caches all entities in the first-level cache inside the Session. This means that when you get to the end of the 70000 items loop (while(results.next()){ ...) the session will contain well over 2GB of data (70000 * 33k).

One way to solve this is to call Session.evict() as soon as you are done with an object, or possibly Session.clear() every few hundred object, since it may be quicker.

Eg.

Code:
while(results.next())
{
     slip = (SlipMnt)results.get(0);
     session.evict(slip);
}


or
Code:
int counter = 0;
while(results.next())
{
    slip = (SlipMnt)results.get(0);
    if (counter++ == 100)
    {
        session.clear();
        counter = 0;
    }
}


Top
 Profile  
 
 Post subject: Scrollable Results Out of Memory error
PostPosted: Thu Oct 30, 2008 10:54 pm 
Newbie

Joined: Thu Oct 30, 2008 3:26 am
Posts: 10
Hi,

Either of session.evict(slip) or
session.clear() didn't solved the problem.

Still i am getting the out of memory error.

But when tested the code for Oracle 10G, its working fine without any memory error eventhough i didn't used session.eveict() or session.clear().

I am facing the same problem with my sql too.

Is this a Bug in hibernate that, its not properly using the scrollable results for MSSQL server & My SQL.

Please help me.

Thanks,
Sridhar Reddy .R


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 31, 2008 3:25 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Maybe it is an issue with the JDBC driver. We had some problems with MySQL some time ago. It was before MySQL had proper support for server-side cursors, which meant that the JDBC driver loaded the entire result set in memory before returning the result. With MySQL you need to add useCursorFetch=true&useServerPrepStmts=true to the connection url.

Eg.
Code:
jdbc:mysql://localhost/base2?useCursorFetch=true&useServerPrepStmts=true


Top
 Profile  
 
 Post subject: same problem using HibernatePage
PostPosted: Sun Dec 14, 2008 6:37 pm 
Beginner
Beginner

Joined: Tue May 03, 2005 11:45 pm
Posts: 43
I'm seeing the same problem. I tried adding those two statements to my url via the hibernate config like this:

Code:
<bean id="appDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>


      <property name="url" value="jdbc:mysql://localhost/dbanme?useCursorFetch=true&useServerPrepStmts=true"/>
      <property name="username" value="user"/>
      <property name="password" value="password"/>



but I get this parse exception even if I add the semicolon like it wants. So I deployed to my server using just useCursorFetch=true

Code:
Context initialization failed
org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 217 in XML document from ServletContext resource [/WEB-INF/database.xml] is invalid; nested exception is org.xml.sax.SAXParseException: The reference to entity "useServerPrepStmts" must end with the ';' delimiter.
   at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.doLoadBeanDefinitions(XmlBeanDefinitionReader.java:359)
   at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:303)
   at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:280)
   at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:142)
   at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:158)
   at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:124)
   at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:92)
   at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:101)
   at org.springframework.context.support.AbstractApplicationContext.obtainFreshBeanFactory(AbstractApplicationContext.java:395)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:330)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:244)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:187)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3764)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4216)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:831)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:720)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490)
   at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1218)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:293)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
   at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1306)
   at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1570)
   at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1579)
   at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1559)
   at java.lang.Thread.run(Thread.java:613)



This is my out of memory stack trace

Code:
ava.lang.OutOfMemoryError: Java heap space
   at com.mysql.jdbc.ByteArrayBuffer.getBytes(ByteArrayBuffer.java:128)
   at com.mysql.jdbc.MysqlIO.extractNativeEncodedColumn(MysqlIO.java:3749)
   at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:3709)
   at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1320)
   at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2270)
   at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:423)
   at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1960)
   at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1385)
   at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1138)
   at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:675)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
   at org.hibernate.loader.Loader.scroll(Loader.java:2301)
   at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:464)
   at org.hibernate.hql.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:413)
   at org.hibernate.engine.query.HQLQueryPlan.performScroll(HQLQueryPlan.java:269)
   at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1233)
   at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:90)
   at com.chaos.snapmusic.dbsupport.HibernatePage.getScrollPageInstanceWithTotalByScroll(HibernatePage.java:161)
   at com.chaos.snapmusic.dbsupport.HibernatePage.getHibernatePageInstance(HibernatePage.java:65)
   at com.chaos.snapmusic.dbsupport.HibernatePage.getHibernatePageInstance(HibernatePage.java:44)
   at com.chaos.snapmusic.dao.GenericHibernateDAO.getPageAllRows(GenericHibernateDAO.java:116)


The other interesting thing I just noticed is that using a criteria query I can get further than I can with using the session.createQuery() like below via getPageAllRows(). The query.scroll() fails very quickly with the out of memory error. Perhaps this is because HibernatePage isn't setting a fetch size?

Code:
   Criteria criteria = sessionFactory.getCurrentSession().createCriteria(persistentClass);
      criteria.setFetchSize(fetchSize);
        ScrollableResults results = criteria.scroll(ScrollMode.SCROLL_SENSITIVE);
     results.last();


the results.last() call fails with this:

Code:
aused by: com.mysql.jdbc.OperationNotSupportedException: Operation not supported for streaming result sets
        at com.mysql.jdbc.RowDataCursor.notSupported(RowDataCursor.java:452)
        at com.mysql.jdbc.RowDataCursor.beforeLast(RowDataCursor.java:270)
        at com.mysql.jdbc.ResultSetImpl.last(ResultSetImpl.java:6992)
        at org.hibernate.impl.ScrollableResultsImpl.last(ScrollableResultsImpl.java:104)



Code:
    public Page getPageAllRows(int pageNumber, int pageSize) throws Exception
    {
       String queryStr = "from " + persistentClass.getSimpleName() + " t order by t.id";
       Query query = sessionFactory.getCurrentSession().createQuery(queryStr);
       return HibernatePage.getHibernatePageInstance(query, pageNumber, pageSize);
    }


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 15, 2008 3:02 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Quote:
but I get this parse exception even if I add the semicolon like it wants. So I deployed to my server using just useCursorFetch=true


I don't know were you added the semicolon, but the error message is a bit misleading. It is the '&' that needs to be escaped and the proper way to do that in XML is with '&amp;' so the property should be:

Code:
<property name="url" value="jdbc:mysql://localhost/dbanme?useCursorFetch=true&amp;useServerPrepStmts=true"/>


Top
 Profile  
 
 Post subject: Re: Scrollable Results Out of Memory error
PostPosted: Wed Apr 28, 2010 2:05 pm 
Newbie

Joined: Wed Aug 12, 2009 1:46 pm
Posts: 7
I've had a similar problem when using the Criterion API. I want to scroll through a large set of blobs,
without using any memory.

I found that using critera.setCacheMode(CacheMode.IGNORE) seems to have solved the problem for me.

Code:
      
Criteria critera = session.createCriteria(MyObject.class);
critera.add(Restrictions.eq("someProperty", someValue));
critera.setProjection(
    Projections.projectionList()
       .add(Projections.property("id"))
       .add(Projections.property("data")));

critera.setCacheMode(CacheMode.IGNORE);
critera.setFetchSize(200);
ScrollableResults results = critera.scroll(ScrollMode.FORWARD_ONLY);


So in my iteration loop:
Code:
while (results.next) {
System.out.println(results.get());
session.clear();
}


Top
 Profile  
 
 Post subject: Re: Scrollable Results Out of Memory error
PostPosted: Wed Apr 28, 2010 5:00 pm 
Expert
Expert

Joined: Sat Jan 17, 2004 2:57 pm
Posts: 329
Location: In the basement in my underwear
The other piece that helps you honeybunny is the ScrollMode.FORWARD_ONLY, I ran into mem issues previously where I was clearing the session every x records but the ScrollableResultSet was also caching the results I had moved past (I assume in case I wanted to scroll back)

_________________
Some people are like Slinkies - not really good for anything, but you still can't help but smile when you see one tumble down the stairs.


Top
 Profile  
 
 Post subject: Re: Scrollable Results Out of Memory error
PostPosted: Mon Jan 03, 2011 9:49 am 
Newbie

Joined: Mon Jan 03, 2011 9:44 am
Posts: 1
Don't forget to close the ScrollableResults object when you are done with it. Otherwise the memory is not freed until you close the session.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.