Repeated insert operation gets progressively slower

rmann · **Joined:** Sat Jan 06, 2007 3:46 pm **Posts:** 15

Hibernate version: 3.2.6.ga

Mapping documents: Done with annotations.

Code between sessionFactory.openSession() and session.close():

Name and version of the database you are using: MySQL 5.1.18-beta

I have what I think is a fairly basic Hibernate (w/Annotations)/Struts/Spring webapp running on Resin. I use the OpenSessionInViewFilter provided by Spring. I have two entities, a Satellite, which can have 0 or more TLEs. In general things seem to work, but I have a LoadData operation that parses a text file of TLEs, adding them to the DB. As the operation progresses (over some 30,000 TLE records), it gets progressively slower and slower. This smells like some kind of caching problem, but every suggestion I've come across doesn't quite seem to apply to my scenario.

I have a "Service" object that provides some operations, such as "Satellite findSatelliteByCatalogId()" and "boolean tleExistsForSatAndDate()". Spring wraps a transaction manager around this Service object. In a Struts action, I have code that loops over the records in the file, parsing out SatTLE objects (which just contain the values parsed until they can be persisted via Hibernate).

The code to process a parsed SatTLE record looks basically like this (irrelevant details removed):

Code:

void update(SatTLE inTLE)
{
   IService s = getService();
   
   //   Look for an existing satellite for this TLE…
   
   Satellite sat = s.findSatelliteByCatalogId(inTLE.mObjId);
   if (sat == null)
   {
      //   Satellite doesn't exist, create a new one…
      
      sat = new Satellite();
      sat.setCatalogId(new Long(inTLE.mObjId));
      sat.setName(inTLE.mName);
      s.save(sat);
   }
   
   //   We have the satellite, now add the TLE…
   
   boolean foundTLE = s.tleExistsForSatAndDate(sat, inTLE.mEpochYear, inTLE.mEpochDay);
   if (!foundTLE)
   {
      TLE tle = new TLE();
      tle.setAddedOnDate(Calendar.getInstance());
      tle.setEpochYear(inTLE.mEpochYear);
      tle.setEpochDay(inTLE.mEpochDay);
      sat.add(tle);
   }
}

I measured the times for the calls to findSatelliteByCatalogId() and tleExistsForSatAndDate(). The time spent in each (measured across 100 invocations) steadily increases the longer the operation is run (update can be called several thousand times). Here are the implementations of findSatelliteByCatalogId() and tleExistsForSatAndDate():

Code:

public
Satellite
findSatelliteByCatalogId(long inCatalogId)
{
    Session session = getCurrentSession();
    
    try
    {
        Satellite result = (Satellite) session
                                .createQuery("from Satellite t where t.mSpaceTrackCatalogId=:id")
                                .setLong("id", inCatalogId)
                                .setReadOnly(true)
                                .setMaxResults(1)
                                .uniqueResult();
        
        return result;
    }
    
    finally
    {
        releaseSession(session);
    }
}

public
boolean
tleExistsForSatAndDate(Satellite inSat, int inEpochYear, double inEpochDay)
{
    Session session = getCurrentSession();
    
    try
    {
        Long r = (Long) session
                        .createQuery("select count(*) from TLE t where t.mSatellite=:sat "
                                        + "and t.mEpochYear=:year and t.mEpochDay=:day")
                        .setParameter("sat", inSat)
                        .setInteger("year", inEpochYear)
                        .setDouble("day", inEpochDay)
                        .setReadOnly(true)
                        .setMaxResults(1)
                        .uniqueResult();
        
        return r.longValue() > 0;
    }
    
    finally
    {
        releaseSession(session);
    }
}

The slowdown does not seem to be related to how many items are in the DB, but rather on how long a given load operation has been running. This implies that caches are getting full, perhaps. I saw a recommendation in the manual to call flush() and clear(), but when I tried to do this, Hibernate complained that I wasn't in a transaction, which is correct: the transactions are begun and ended automagically by Spring around each of the searches above.

There are definitely more queries going on than I would like, but per update that's constant; it doesn't explain why the performance falls off so badly as the operation progresses.

Any suggestions of what to look into would be greatly appreciated. Thanks!

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Before investigating any further Hibernate/Spring cause,
see if disabling your table(s) indexes during the insertion makes any difference (you can always recreate then once finished).

rmann · **Joined:** Sat Jan 06, 2007 3:46 pm **Posts:** 15

The only index, other than the primary keys on my tables, was one I created in an effort to speed up the search on catalogId. I dropped that index, but it didn't seem to have any effect.

Note that the time is spent in the two query routines findSatelliteByCatalogId(), and tleExistsForSatAndDate(), which don't actually update the index.

rmann · **Joined:** Sat Jan 06, 2007 3:46 pm **Posts:** 15

The only index, other than the primary keys on my tables, was one I created in an effort to speed up the search on catalogId. I dropped that index, but it didn't seem to have any effect.

Note that the time is spent in the two query routines findSatelliteByCatalogId(), and tleExistsForSatAndDate(), which don't actually update the index.