Memory problem with Query

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

I'm having a problem paging through a large number of records with setMaxResults() and setFirstResult() using Hibernate with Sybase Anywhere. According to the documentation, query results are not cached by default so I'm unsure where the problem is coming in. Here is a snippet of my code:

Code:

      Session session = ConnectionFactory.getInstance().getSession();

      try
      {
         
         String queryString = "select reference from com.fedex.fsme.model.Reference reference order by reference.name";
         Query query = session.createQuery(queryString);
         query.setMaxResults(size);
         query.setFirstResult((page - 1) * size);

         return query.list();
      }
      catch (HibernateException e)
      {
         System.err.println("Hibernate Exception: " + e.getMessage());
         throw new RuntimeException(e);
      }

      
      finally
      {
         if (session != null)
         {
            try
            {
               session.close();
            }
            catch (HibernateException e)
            {
               System.err.println("Hibernate Exception: " +
                   e.getMessage());
               throw new RuntimeException(e);
            }
         }
      }

Can someone please tell me what might be missing to cause this section of code to accumulate memory?

Derek Greer

emmanuel · **Posted:** Wed Jun 02, 2004 6:14 pm

too few info

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

I'm using Hibernate version: 2.1.2. Here is my mapping:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class name="com.fedex.fsme.model.Reference" table="referencetable" >
        <id name="id" column="referencecode">
            <generator class="identity"/>
        </id>
        <property name="name" column="referencename" type="java.lang.String" />
        <property name="type" column="referencetype" type="java.lang.Character" />
    </class>
</hibernate-mapping>

Here is the hibernate.properties I'm using:

Code:

hibernate.query.substitutions true 1, false 0, yes 'Y', no 'N'
hibernate.query.imports net.sf.hibernate.test, net.sf.hibernate.eg


## Sybase Anywhere
hibernate.dialect net.sf.hibernate.dialect.SybaseAnywhereDialect
hibernate.connection.driver_class com.sybase.jdbc2.jdbc.SybDriver
hibernate.connection.username dba
hibernate.connection.password fsmanager
hibernate.connection.url jdbc:sybase:Tds:localhost:49152/fsme

#################################
### Hibernate Connection Pool ###
#################################

hibernate.connection.pool_size 1
hibernate.statement_cache.size 25

hibernate.show_sql true
hibernate.jdbc.batch_size 0
hibernate.jdbc.use_scrollable_resultset false
hibernate.jdbc.use_streams_for_binary true

And here is a segment of my logs:

Code:

Jun 3, 2004 10:43:57 AM net.sf.hibernate.connection.DriverManagerConnectionProvider closeConnection
FINEST: returning connection to pool, pool size: 1
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl afterTransactionCompletion
FINEST: transaction completion
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl <init>
FINE: opened session
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl find
FINEST: find: select reference from com.fedex.fsme.model.Reference reference order by reference.name
Jun 3, 2004 10:43:57 AM net.sf.hibernate.engine.QueryParameters traceParameters
FINEST: named parameters: {}
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl flushEverything
FINEST: flushing session
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl flushEntities
FINEST: Flushing entities and processing referenced collections
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl flushCollections
FINEST: Processing unreferenced collections
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl flushCollections
FINEST: Scheduling collection removes/(re)creates/updates
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl flushEverything
FINE: Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects

Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl flushEverything
FINE: Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl autoFlushIfRequired
FINEST: Dont need to execute flush
Jun 3, 2004 10:43:57 AM net.sf.hibernate.hql.QueryTranslator logQuery
FINE: HQL: select reference from com.fedex.fsme.model.Reference reference order by reference.name
Jun 3, 2004 10:43:57 AM net.sf.hibernate.hql.QueryTranslator logQuery
FINE: SQL: select reference0_.referencecode as referenc1_, reference0_.referencename as referenc2_, reference0_.referencetype as referenc3_ from referencetable reference0_ order by  reference0_.referencename
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.BatcherImpl logOpenPreparedStatement
FINEST: about to open: 0 open PreparedStatements, 0 open ResultSets
Jun 3, 2004 10:43:57 AM net.sf.hibernate.connection.DriverManagerConnectionProvider getConnection
FINEST: total checked-out connections: 0
Jun 3, 2004 10:43:57 AM net.sf.hibernate.connection.DriverManagerConnectionProvider getConnection
FINEST: using pooled JDBC connection, pool size: 0
Hibernate: select reference0_.referencecode as referenc1_, reference0_.referencename as referenc2_, reference0_.referencetype as referenc3_ from referencetable reference0_ order by  reference0_.referencename
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.BatcherImpl getPreparedStatement
FINE: select reference0_.referencecode as referenc1_, reference0_.referencename as referenc2_, reference0_.referencetype as referenc3_ from referencetable reference0_ order by  reference0_.referencename
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.BatcherImpl getPreparedStatement
FINEST: preparing statement
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader doQuery
FINEST: processing result set
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '65657' as column: referenc1_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader getRow
FINE: result row: 65657
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader loadFromResultSet
FINEST: Initializing object from ResultSet: 65657
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader hydrate
FINEST: Hydrating entity: com.fedex.fsme.model.Reference#65657
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '000001-000580' as column: referenc2_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '1' as column: referenc3_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '65658' as column: referenc1_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader getRow
FINE: result row: 65658
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader loadFromResultSet
FINEST: Initializing object from ResultSet: 65658
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader hydrate
FINEST: Hydrating entity: com.fedex.fsme.model.Reference#65658
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '000001-000581' as column: referenc2_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '1' as column: referenc3_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '65659' as column: referenc1_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader getRow
FINE: result row: 65659
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader loadFromResultSet
FINEST: Initializing object from ResultSet: 65659
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader hydrate
FINEST: Hydrating entity: com.fedex.fsme.model.Reference#65659
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '000001-000588' as column: referenc2_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '1' as column: referenc3_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '65660' as column: referenc1_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader getRow
FINE: result row: 65660
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader loadFromResultSet
FINEST: Initializing object from ResultSet: 65660
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader hydrate
FINEST: Hydrating entity: com.fedex.fsme.model.Reference#65660
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '000001-000589' as column: referenc2_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '1' as column: referenc3_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '65661' as column: referenc1_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader getRow
FINE: result row: 65661
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader loadFromResultSet
FINEST: Initializing object from ResultSet: 65661
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader hydrate
FINEST: Hydrating entity: com.fedex.fsme.model.Reference#65661
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '000001-000599' as column: referenc2_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.type.NullableType nullSafeGet
FINEST: returning '1' as column: referenc3_
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader doQuery
FINEST: done processing result set (5 rows)
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.BatcherImpl logClosePreparedStatement
FINEST: done closing: 0 open PreparedStatements, 0 open ResultSets
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.BatcherImpl closePreparedStatement
FINEST: closing statement
Jun 3, 2004 10:43:57 AM net.sf.hibernate.loader.Loader doQuery
FINEST: total objects hydrated: 5
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: resolving associations for [com.fedex.fsme.model.Reference#65657]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: done materializing entity [com.fedex.fsme.model.Reference#65657]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl finalize
FINE: running Session.finalize()
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: resolving associations for [com.fedex.fsme.model.Reference#65658]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: done materializing entity [com.fedex.fsme.model.Reference#65658]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: resolving associations for [com.fedex.fsme.model.Reference#65659]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: done materializing entity [com.fedex.fsme.model.Reference#65659]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: resolving associations for [com.fedex.fsme.model.Reference#65660]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: done materializing entity [com.fedex.fsme.model.Reference#65660]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: resolving associations for [com.fedex.fsme.model.Reference#65661]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeEntity
FINE: done materializing entity [com.fedex.fsme.model.Reference#65661]
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl initializeNonLazyCollections
FINE: initializing non-lazy collections
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl close
FINEST: closing session
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl disconnect
FINE: disconnecting session
Jun 3, 2004 10:43:57 AM net.sf.hibernate.connection.DriverManagerConnectionProvider closeConnection
FINEST: returning connection to pool, pool size: 1
Jun 3, 2004 10:43:57 AM net.sf.hibernate.impl.SessionImpl afterTransactionCompletion
FINEST: transaction completion

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

Can anyone help?

christian · **Posted:** Fri Jun 04, 2004 9:54 am

Use a profiler, your code looks good.

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

Unfortunately I'm not familiar with using profilers, but I may have to take the time to learn if no one else knows what the problem is. If it does what I've been told profilers do, I expect it will indicate that memory significantly increases everytime I do a return query.list(). My test application is fairly small (I've included it in its entirity below), and I don't see anywhere else within the code that would interatively be allocating memory, so I suspect it is a Hibernate problem.

Here is my full source:

Code:

package com.fedex.fsme.reference;

import java.util.Iterator;
import java.util.List;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Query;
import net.sf.hibernate.Session;

import com.fedex.fsme.model.Reference;

public class ReferenceServiceTwo
{

   private static ReferenceServiceTwo instance = null;

   public static synchronized ReferenceServiceTwo getInstance()
   {
      if (instance == null)
      {
         instance = new ReferenceServiceTwo();
      }

      return instance;
   }

   public int getReferenceCount()
   {
      Session session = ConnectionFactory.getInstance().getSession();
      int count = -1;

      try
      {
         count = ((Integer) session.iterate("select count(*) from com.fedex.fsme.model.Reference").next()).intValue();

      }
      catch (HibernateException e)
      {
         System.err.println("Hibernate Exception: " + e.getMessage());
         throw new RuntimeException(e);
      }

      finally
      {
         if (session != null)
         {
            try
            {
               session.close();
            }
            catch (HibernateException e)
            {
               System.err.println("Hibernate Exception: " + e.getMessage());
               throw new RuntimeException(e);
            }
         }
      }

      return count;
   }

   public List getReferenceList(int page, int size)
   {
      Session session = ConnectionFactory.getInstance().getSession();

      try
      {
         String queryString = "select reference from com.fedex.fsme.model.Reference reference order by reference.name";
         Query query = session.createQuery(queryString);
         query.setMaxResults(size);
         query.setFirstResult((page - 1) * size);

         return query.list();
      }
      catch (HibernateException e)
      {
         System.err.println("Hibernate Exception: " + e.getMessage());
         throw new RuntimeException(e);
      }

      finally
      {
         if (session != null)
         {
            try
            {
               session.close();
            }
            catch (HibernateException e)
            {
               System.err.println("Hibernate Exception: " + e.getMessage());
               throw new RuntimeException(e);
            }
         }
      }
   }

   public static void main(String[] args)
   {
      int count = ReferenceServiceTwo.getInstance().getReferenceCount();

      for (int page = 0, i = 0; i < 100; page++, i += 5)
      {
         printList(page, 5);
      }
   }

   private static void printList(int page, int size)
   {
      // retrieve the entire list of references
      List referenceList = ReferenceServiceTwo.getInstance().getReferenceList(page, size);

      for (Iterator iter = referenceList.iterator(); iter.hasNext();)
      {
         Reference element = (Reference) iter.next();
         System.out.println("reference:" + element.getName());
      }
   }
}

If the code looks ok, then my assumption is that the SybaseAnywhereDialect is deficient in some way. I do know from problems our group have discovered recently that the Sybase JConnect driver has an issue with scrollable result sets (see http://hoth.stsci.edu/public/java1.2/jC ... htm#X18457).
Basically, JConnect supports scrollable result sets by caching every record in memory. Of course, I've set my hibernate.jdbc.use_scrollable_resultset to false, but I wouldn't know if the SybaseDialect or the SybaseAnywhereDialect is using TYPE_SCROLL_INSENSITIVE or not without looking at the code (unless a Hibernate developer can tell me). Of course I'll have to continue researching this on my own, but I'm afraid using a profiler is only going to tell me what I already suspect ... just a few days later. Unfortunately I may have to abandon the idea of using Hibernate by then as I can't afford to spend several weeks getting this to work or ultimately finding out that it won't work ever unless I switch databases. Any further help will be greatly appreciated.

Derek Greer

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

After looking at the code, it doesn't appear that the code is using TYPE_SCROLL_INSENSITIVE in the event hibernate.jdbc.use_scrollable_resultset is set to false and is falling through to a conn.prepareStatement(sql) call which by default uses TYPE_FORWARD_ONLY. Therefore, it doesn't appear that the memory is being accumulated by the JConnect driver itself. Is it somehow possible Hibernate is caching the objects? Do I need to put in some explicit call to empty the session of its cache?

Derek Greer

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

The main function I listed eariler actually loops while i < count. I forgot to change it back before I cut and pasted. Can anyone help?

Derek

gavin · **Posted:** Mon Jun 07, 2004 9:38 am

derekg wrote:

The main function I listed eariler actually loops while i < count. I forgot to change it back before I cut and pasted. Can anyone help?

Derek

Um. The session is a cache. If you do multiple queries in the same session, memory usage accumulates, obviously. You may use evict() or clear() to release memory.

In future, please search the forum for similar problems before posting.

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

To all:

I appologize. It seems that my app wasn't accumulating as much memory as I originally thought. I believe I originally confused my test app's javaw process with my eclipse javaw process (which for some reason seemed to be jumping significantly while running my test). Anyway, it appears my example is running correctly.

To Gaven:

Quote:

Um. The session is a cache. If you do multiple queries in the same session, memory usage accumulates, obviously. You may use evict() or clear() to release memory.

I'm not sure I understand your response. I am only doing one query per session and then closing it. In other words, I open the session, perform a query on the page of data I want, and then close the session. As I pointed out eariler, the documentation states that query results are not cached by default, so my original question was why the results would still be in memory after I closed the session. Of course, now that I've correctly identified which process was associated with my test, it doesn't seem that it was caching after all. After reviewing my source, are you saying that I should be using evict() or clear() to avoid caching?

Quote:

In future, please search the forum for similar problems before posting.

You are making the assumption that I didn't first search the forum. Upon thinking that my objects were being cached when using the Query class, I did search to see if others were having similar problems. Since the Query class isn't suppose to be caching, discussions concerning using the evict() and clear() to release the cache do not pertain to the problem I thought I was having. Thanks.

Derek

gavin · **Posted:** Mon Jun 07, 2004 2:01 pm

I deduced from

(1) you had a memory leak
(2) you were doing a loop w/o showing us

that

(3) your loop was reusing the same session

Since (1) was not true, the conclusion (3) was also not necessarily true.

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

Hey Gaven. Since I have your attention, do you think you could take a look at my posting on "Paging with Sybase Anywhere"? I'm seeking answers mainly to two questions:

1) Is the introduction of Sybase Anywhere 9's "START AT" more efficient than the way the SybaseDialect currently implements the setFirstResult() and setMaxResults()? If so, are there any plans to create a SybaseAnywhere9Dialect that takes advantages of these improvements?

2) Were you aware that Hibernate throws exceptions when setting scrollable result sets to true when used with Sybase Anywhere?

Thanks.

Derek

gavin · **Posted:** Mon Jun 07, 2004 5:48 pm

1) You are welcome to submit a patch
2) I am not aware, and don't care ... if Sybase does not support scrollable result sets, you can't use them. That's what

hibernate.jdbc.use_scrollable_resultset

is for....

derekg · **Joined:** Sun May 09, 2004 10:34 pm **Posts:** 16

First, have I offended you in some way? You seem overly harsh, quick, and judgemental. I'm just here in the "Hibernate Beginners" forum as a "beginner". You comments come across as "if you don't like it, fix it yourself" and "I don't care about that particular bug". I'm simply trying to evaluate whether Hibernate is a good choice for my group's project and trying to alert the development team of problems I see in my evaluation. Please don't take offense.

Concerning your statements, actually Sybase Anywhere does support scrollable result sets ... just not efficiently. Sybase recommends that they only be used for small result sets.

Given that Sybase does support them, using the SybaseAnywhereDialect should work properly if hibernate.jdbc.use_scrollable_resultset is set to true. If it is reccomended by Hibernate that it not be used, this property should be defaulted to false.

You probably know better than I what hibernate.jdbc.use_scrollable_resultset is meant for, but I would think that this setting should be used to turn off scrollable result sets when the underlying database supports them. Determining whether they are supported should be a function of the DatabaseMetaData class. Whether the underlying database supports scrollable resultsets or not, the configuration shouldn't cause exceptions in my opinion.

Concerning Sybase 9, I was hoping since you developed the Sybase portion of the code that you might have good insight concerning the efficiency of paging with Sybase 8 vs. 9. It's unfortunate for the Sybase users of Hibernate if you simply don't care.

Derek

gavin · **Posted:** Mon Jun 07, 2004 8:48 pm

Actually I answered your questions as quickly as possible, I did not have time to insult you nor to spend half an hour making you feel warm and fuzzy,

Now I am annoyed with you.