(originally posted this under "tools" by mistake, sorry)

Our application has just run into speed and low memory issues, since we have recently added more data (a little less then doubled the size of our db) to the system. SQL Server 2000 is being brought to its knees, and CPU on the machine maxed out at 100% with a relatively low number of concurrent db connections. (5>8).

running perofrmance monitor (win XP), i can watch as a read request is executed (for 300-400 objects): the number of lock requests spikes, and the processor time spikes.

I am wondering if I am taking advantage of hibernate's transaction/concurreny/locking models to best effect. In reading chapter 11, it appears we are using session-per-request as we should, using OpenSessionInView filter, and the HibernateUtil class. I have included the code we use in hibernateUtil and OpensessioninView below, along with our typical read, and save functions. Also included are our pooling and misc settings from hibernate config.

Am I missing something in the way I manage sessions and transactions that I should be using to improve my performance and scalability?

Is locking necessary on reads?

should I be setting jdbc.fetch_size to a non zero value?

in short...what am i dong wrong? :)

Thanks

Paul

<b>Hibernate version:</b>
2.1
<b>hibernate config settings:</b>

<!--pool settings -->
<property name="dbcp.maxActive">100</property>
<property name="dbcp.whenExhaustedAction">1</property>
<property name="dbcp.maxWait">120000</property>
<property name="dbcp.maxIdle">10</property>
<property name="dbcp.ps.maxActive">100</property>
<property name="dbcp.ps.whenExhaustedAction">1</property>
<property name="dbcp.ps.maxWait">120000</property>
<property name="dbcp.ps.maxIdle">10</property>

<!-- ## Miscellaneous Settings-->

<property name="show_sql">true</property>
<property name="hbm2ddl.auto">update</property>
<property name="use_outer_join">true</property>
<property name="max_fetch_depth">1</property>
<property name="cache.region_prefix">hibernate.test</property>
<property name="cache.use_query_cache">true</property>
<property name="jdbc.use_streams_for_binary">true</property>
<property name="hibernate.cache.provider_class">net.sf.ehcache.hibernate.Provider</property>



<b>Code between sessionFactory.openSession() and session.close(): </b>
in hibernateUtil class:
public static final ThreadLocal session = new ThreadLocal();

public static Session currentSession() throws HibernateException
{
Session s = (Session) session.get();

// Open a new Session, if this Thread has none yet
if (s == null)
{
s = sessionFactory.openSession();
session.set(s);
}
return s;
}

public static void closeSession() throws HibernateException
{
Session s = (Session) session.get();
session.set(null);
if (s != null && s.isOpen())
{
s.close();
}
}

in OpenSessioninViewFilter class:
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
throws IOException, ServletException {
try {
chain.doFilter(request, response);
} finally {
try {
HibernateUtil.closeSession();
} catch (HibernateException ex) {
throw new ServletException(ex);
}
}
}

a typical read function

public List getAll() {
List objs = new ArrayList();

try {
Session session = HibernateUtil.currentSession();
Transaction trx = session.beginTransaction();
objs = session.createQuery(SELECT_ALL).list();
trx.commit();
} catch (ObjectNotFoundException e) {
log.error("object not found: hsql=" + SELECT_ALL, e);
} catch (HibernateException e) {
log.error("hsql=" + SELECT_ALL, e);
}
return objs;
}

a typical save function:

public int save(Case obj) {
int retVal = -1;
try {
Session session = HibernateUtil.currentSession();
Transaction trx = session.beginTransaction();
session.saveOrUpdate(obj);
trx.commit();
retVal = obj.getId().intValue();
} catch (HibernateException e) {
log.error("hsql=" + SELECT_ALL, e);
}
return retVal;
}

<b>Full stack trace of any exception that occurs:</b>
no exception
<b>Name and version of the database you are using:</b>
SQL Server 2000
The generated SQL (show_sql=true):

a few generated queries:

Hibernate: select inventoryi0_.ID as ID0_, inventoryi0_.CAT_NUM as CAT_NUM0_, inventoryi0_.DESCRIPTION as DESCRIPT3_0_, inventoryi0_.MGMT as MGMT0_, inventoryi0_.S2APH as S2APH0_
, inventoryi0_.LSTPC as LSTPC0_, inventoryi0_.UTMES as UTMES0_, inventoryi0_.PTYPE as PTYPE0_, inventoryi0_.PTYPEDESC as PTYPEDESC0_, inventoryi0_.ACTIVE as ACTIVE0_, inventoryi0
_.IGNORE_LOT_CODE as IGNORE_11_0_, inventoryi0_.OBSOLETE as OBSOLETE0_, inventoryi0_.DCODE as DCODE0_, inventoryi0_.MKDIV as MKDIV0_, inventoryi0_.MKDIVDESC as MKDIVDESC0_, inven
toryi0_.S2APH1 as S2APH10_, inventoryi0_.S2APHDESC as S2APHDESC0_, inventoryi0_.PLINE as PLINE0_, inventoryi0_.PLINEDESC as PLINEDESC0_, inventoryi0_.PSIZE as PSIZE0_, inventoryi
0_.PSIZEDESC as PSIZEDESC0_, inventoryi0_.ITEMG as ITEMG0_, inventoryi0_.PMIGL as PMIGL0_, inventoryi0_.PLNID as PLNID0_, inventoryi0_.PTYP2 as PTYP20_, inventoryi0_.PRICE as PRI
CE0_, inventoryi0_.CHANGED_BY as CHANGED_BY0_, inventoryi0_.CHANGED_DATE as CHANGED28_0_ from TBL_INVENTORY_ITEM inventoryi0_ where inventoryi0_.ID=?

Hibernate: select count(*) as x0_0_ from TBL_KIT_INSTANCE_ITEM kitinstanc0_, TBL_INVENTORY_ITEM_PART inventoryi1_ where (kitinstanc0_.KIT_INSTANCE_ID=789862380 )and(inventoryi1_.
INVENTORY_ITEM_ID=789321886 and kitinstanc0_.INVENTORY_ITEM_PART_ID=inventoryi1_.ID)and(inventoryi1_.INVENTORY_ITEM_PART_STATUS_ID=2 and kitinstanc0_.INVENTORY_ITEM_PART_ID=inv
entoryi1_.ID)


Hibernate: select kitprofile0_.ID as ID, kitprofile0_.QTY as QTY, kitprofile0_.CHANGED_BY as CHANGED_BY, kitprofile0_.CHANGED_DATE as CHANGED_4_, kitprofile0_.INVENTORY_ITEM_ID a
s INVENTOR5_, kitprofile0_.KIT_ID as KIT_ID from TBL_KIT_PROFILE kitprofile0_ where (kitprofile0_.KIT_ID is not null )and(kitprofile0_.KIT_ID=789862292 )and(kitprofile0_.QTY-(sel
ect count(*) from TBL_KIT_INSTANCE_ITEM kitinstanc1_, TBL_INVENTORY_ITEM_PART inventoryi2_ where (kitinstanc1_.KIT_INSTANCE_ID=789862380 )and(inventoryi2_.INVENTORY_ITEM_ID=kitpr
ofile0_.INVENTORY_ITEM_ID and kitinstanc1_.INVENTORY_ITEM_PART_ID=inventoryi2_.ID)and(inventoryi2_.INVENTORY_ITEM_PART_STATUS_ID=2 and kitinstanc1_.INVENTORY_ITEM_PART_ID=inven
toryi2_.ID))>0 )