Scalable design for long live query?


Performance and scalability: Scalable design for long live query?

  1. Scalable design for long live query? (2 messages)

    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?



  2. Check out the ValueListHandler/ValueListIterator design pattern
  3. 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...