PostgreSQL Cursor and COUNT(*) OVER()

Leave a comment

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: