Handling large record sets in JDBC/web app

From: JLParke (jeffersonparke_at_hotmail.com)
Date: 03/23/04

  • Next message: Eric Kaun: "Re: Recommendations on SQL Server JDBC driver?" 
    Date: 23 Mar 2004 07:49:34 -0800

    I am aware that this subject has been discussed in other threads but
    I'd like to start fresh and try to present as many alternatives as I
    can before gathering feedback.

    In a web application that must handle large record sets in a paginated
    form, can anyone recommend a "best" compromise of the seemingly
    opposing requirements involved? As I see it, the constraints include
    application server memory, response time of first page, response time
    between pages, network bandwidth, and architectural robustness.

    Here are a few possible solutions that attempt to balance the
    constraints plus some sample pros and cons:
    1) Query for and cache the complete record set in application server
    memory. (very high memory usage, spikey network usage, slow time to
    first page, fast time between pages)
    2) Query for and cache only the keys in the record set. Query for
    complete record by key list for each page change. (reasonable memory
    usage, reasonable network usage, good time to first page, fast time
    between pages, orderable by arbitrary field(s))
    3) Use equivalent of Oracle's rowcount for each page query. (vendor
    lock-in)
    4) Use equivalent of Oracle's rowid for each page query. (vendor
    lock-in, can't order complete record set by arbitrary field(s))
    5) Use temporary table to store query results.
    6) Use a range clause for every field that is used to order (sort) the
    query.
    7) Use a range of keys for every page query. (probably slow queries
    because range is open ended, can't sort by arbitrary field(s))
    8) Use multi-threaded cache look ahead to retrieve a page or set of
    pages, predicting their usage.

    Can anyone provide: more candidate solutions, more pros/cons,
    testimony to the efficacy of a solution, or pointer to an existing
    framework/product/pattern/model/paper/discussion that seeks to solve
    or inform.

    Here are a few pointers to resources that I have come across, each
    with it's own undesireable or unresolved issues:
    Distributed Result Set Iterator (biggest issue: can't sort complete
    result set by arbitrary field(s)) -
    http://uqconnect.net/~zzblong/papers/pattern.pdf
    Data List Handler (biggest issue: architectural robustness of keeping
    open connections and cursors across requests) -
    http://www.theserverside.com/articles/article.tss?l=DataListHandler
    TheServerSide.com discussion on the subject (not to conclusive, but
    maybe there is no conclusion) -
    http://www.theserverside.com/patterns/thread.tss?thread_id=12899

    Thanks,
    JLParke

  • Next message: Eric Kaun: "Re: Recommendations on SQL Server JDBC driver?"

    Relevant Pages

    • Re: FTS Performance in SQL 2005
      ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
      (microsoft.public.sqlserver.fulltext)
    • Re: FTS Performance in SQL 2005
      ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... its the pipe between the CPU and Memory that could be the bottleneck. ... and that you have a covering index on the columns in the query. ... Plan window will then give you a percentage query cost relative ...
      (microsoft.public.sqlserver.fulltext)
    • Re: FTS Performance in SQL 2005
      ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Possible memory leak using $sth->{NAME} ?
      ... The basic idea behind the original query was to do a complicated ... while still seeing the leak. ... After executing the query, you must do something which triggers the ... results of the query that would make a difference to the memory usage ...
      (perl.dbi.users)
    • RE: Access 2000 - Error #3426 on "AddNew" to RecordsetClone
      ... new record, you need an updatable record set, which a "Snapshot" is not. ... Source" specifically names the correct query. ... > Dim newxrecord As DAO.Recordset ... Because the data control is currently pointing to a NULL record ...
      (microsoft.public.access.modulesdaovba)