| Hi, I am a contributor to EAI Open Source Platform Ikasan (http://www.ikasan.com/). We have a real world implementation of this platform against a Sybase DB with Hibernate as our ORM. We service large amounts of real time data on our bus and we have harvesting and housekeeping processes continually running. We observed locking and blocking spids when under heaving load. We performed a deep dive to discover that Hibernate is not limiting the result set size when issuing queries as follows:
Criteria criteria = session.createCriteria(WiretapFlowEvent.class);
criteria.add(Restrictions.eq("harvested", false));
criteria.setFirstResult(0);
criteria.setMaxResults(housekeepingBatchSize);
criteria.addOrder(Order.asc("timestamp"));
List<WiretapEvent> wiretaps = criteria.list();
return wiretaps;
I have re-mediated this by creating our own Hibernate dialect:
public class IkasanSybaseASE157Dialect extends SybaseASE157Dialect
{
@Override
public boolean supportsLimit()
{
return true;
}
@Override
public LimitHandler buildLimitHandler(String sql, RowSelection selection)
{
return new IkasanSybaseASE157LimitHandler(sql, selection);
}
}
Along with out own limit handler:
public class IkasanSybaseASE157LimitHandler extends AbstractLimitHandler
{
public IkasanSybaseASE157LimitHandler(String sql, RowSelection selection)
{
super(sql, selection);
}
@Override
public String getProcessedSql()
{
String select = sql.substring(0, sql.indexOf("select") + "select".length());
String statement = sql.substring(sql.indexOf("select") + "select".length(), sql.length());
if(selection.getFirstRow() != null && selection.getMaxRows() != null)
{
return select + " top " + (selection.getFirstRow() + selection.getMaxRows()) + statement;
}
else if(selection.getMaxRows() != null)
{
return select + " top " + selection.getMaxRows() + statement;
}
else
{
return sql;
}
}
@Override
public boolean supportsLimit()
{
return true;
}
public int bindLimitParametersAtStartOfQuery(PreparedStatement statement, int index) throws SQLException
{
return 0;
}
public int bindLimitParametersAtEndOfQuery(PreparedStatement statement, int index) throws SQLException {
return 0;
}
}
Is there any reason that this sort of functionality has not been implemented in Hibernate. I realise that Sybase has some limitations and does not support offset. However, i think that my solution goes part way to solving the problem, and I have confirmed that the burden on out database is greatly reduced. Could you please share some thoughts on this? Have I overlooked anything? If not would you consider including this kind of functionality? |
