We're updating the issue view to help you get more done. 

Sybase Dialect not supporting max result - paging

Description

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:

1 2 3 4 5 6 7 8 9 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 our own limit handler:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 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?

Environment

None

Status

Assignee

Guillaume Smet

Reporter

Michael Stewart

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.12

Priority

Major