It is a common problem:user execute a runtime query and
hundreds(or even more) records get found. User navigate
the result set page by page.

Design 1 - stateful

execute only one query and maintain a long live resultset
(or database cursor).

Design 2 - stateless
donnot maintain a long live cursor, each page generates a
new query follow by seek, read and close.

Which design is better?

Design 1 seems to be faster but I doubt it will scale, e.g.
the jdbc connection cannot be return to the pool because of
the pending resultset.

Design 2 seems to be more scalable and although database
should cache the execution plan and data, but there still
will be a performance hit...

I think design 2 maybe better because design 1 let the end
user control the lifecycle of the resultset, isn't that
break the basic principle?

I am wondering what is the common practice for this..

Anybody here working for Google? or the ServerSide?

 


 

Check out the ValueListHandler/ValueListIterator design pattern

I guess, the query is more to do with stateful design or otherwise.
If the query is regarding how to go about designing effieciently stateful query results, then the ValueListHandler pattern is a nice option, that cuts out the EJB overweight.

Answering the primary query, I suppose, its more to do with the context in which its being used. If we know, on an average, the chances of retreiving hundreds or thousands of matching records, then with Java, its always better to go for stateless design option.

Another thing to be considered is that, we know that most of the times, client never goes thru the entire result set.
BUT, If the requirement is that, client always processes the entire resultset, then it makes more sense to make it a stateful design. Mind you, we are compromising on scalabilty (control for the connection is with the client) to gain speed and performance (the response time with which we serve the complete resultset)

My understanding is that, such a requirement is a rare scenario. so, my money would be on stateless design. and, thats what we did in our previous projects too...