November 21, 2012 by huionn
For larger resultset of SQL query, cursor could provide better performance with extra overhead. As I find out in Nuxeo forum (http://forum.nuxeo.com/t/6057/), cursor-based resultset is not used in Nuxeo due to JDBC driver restriction in cursor implementaton.
As I foresee (which may not happen in reality) I need an efficient way to query through CoreSession.queryAndFetch() for potentially large result in future. As I can control my client application for pagination, I think the limitation of cursor can somehow be overcome with user interface. For personal experiment, I tried to extend ResultSetQueryResult with my own CursorResultSetQueryResult. I also extend JDBCMapper and JDBCB to handle special query type with CursorResultSetQueryResult.
In order to support ‘limit’ and ‘offset’, I append ‘limit’ and ‘offset’ at the end of SQL. In order to support count (or size()) if it is required, I insert “COUNT(*) OVER() AS _total” in the SELECT clause. The result of my experiment is that my CursorResultSetQueryResult support cursor, limit, offset and count in single SQL execution.
Honestly, I don’t think the implementation is robust enough and efficient in all scenarios (especially complex queries). Nevertheless, I will definitely need a “safe” way to execute some queries that may have large resultset because my application is SaaS-based. I don’t want an unfiltered query eats up all server resources and affects other concurrent users.