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:
I have re-mediated this by creating our own Hibernate dialect:
Along with our own limit handler:
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?