Retrieving and presenting huge data

Discussions

General J2EE: Retrieving and presenting huge data

  1. Retrieving and presenting huge data (5 messages)

    Hi,

    I have a query that returns millions of records from Oracle database, and I need to present this data to the user in JSPs. I need to show this data page by page. So I will have buttons called "Next", "Previous" in the JSP and allow the users to navigate through the records. While pressing the
    Next button I don't want to connect to database and show the next page.

    Which is the best way to achieve this?

    Any feedback is greatly appreciated.

    Thanks,
    Nagendra.
  2. If you are really presenting a million records worth of data, is impractical to load all of this data into memory at one time. It would be better to retrieve only the data for each page.

    Unfortunately, this means a new database connection for each "next". If you really want to minimize database connections, you might fetch and cache 100 or so records at a time instead of the 25 per page, but I doubt that will help performance much. If you have a decent connection pooling mechanism, the "database-lookup-per-page" should not take much time.

    What you should really concentrate on is a good query or filter mechanism. It is unreasonable to expect the user to wade through a million records to find their data. You need to provide some sort of mechanism to reduce the size of the data set so they can locate their data. After all, that is what SQL is designed to do.
  3. What you should really concentrate on is a good query or filter mechanism.
    This is good advice!

    In general if you are looking for a mechanism to handle displaying data on a page x page basis without requerying the DB check out the Value List Handler pattern.

    An writeup of the pattern is available here:
    http://java.sun.com/blueprints/corej2eepatterns/Patterns/ValueListHandler.html

    There is a discussion/implementation available here:
    http://theserverside.com/discussions/thread.tss?thread_id=26185

    HTH
  4. This is a classical problem with most of the queries with large number of result rows.

    The problem can be addressed in two ways: Functionally and Technically.

    Functionally :

    You can discuss various access patterns for the information presented with the end users.

    i)
  5. This is a classical problem with most of the queries with large number of result rows.

    The problem can be addressed in two ways: Functionally and Technically.

    Functionally :

    You can discuss various access patterns for the information presented with the end users.

    i) You can divide search in multiple pages. So a particular type of items will be searched from one screen and second type of items can be searched from other screens.

    ii) YOu can enforce filters that can limit the search result. It will be better for users also as they have to navigate lessers pages to find the information they are looking for.

    Technical :

    i)
  6. This is a classical problem with most of the queries with large number of result rows.

    The problem can be addressed in two ways: Functionally and Technically.

    Functionally :

    You can discuss various access patterns for the information presented with the end users.

    i) You can divide search in multiple pages. So a particular type of items will be searched from one screen and second type of items can be searched from other screens.

    ii) YOu can enforce filters that can limit the search result. It will be better for users also as they have to navigate lessers pages to find the information they are looking for.

    Technically :

    FOr such a large query results, it is definitely not recommended to get all the data in memory. As it will deteriorate performance of system and increase response time for the end user.

    So possible Problems Can be:

    i) Search takes more time.
    ii) Search result will block connection and increase network traffic.
    iii) User may have to wait for longer time to get to the place they want to go.

    Options:
    i) You can do window based caching where you always cache 5 pages in memory. Everytime user accesses 6th page you wipe out cache and reload next 5 pages.

    ii) Cache the ResultSet pointer in session and use the same to navigate. This may be an inefficient way because the transaction is not completed for the time result set is there in memory and it may block connections as connection will not be released. Also there are possibility of conneciton leaks so you have to be very careful if you want to do that.

    iii) Use asynchronous process to fire the query, whcih will return you a token and will populate certain temporary table with the result rows. You can poll the table and as soon as you get the required data you can access them and return it to client. By the time user will be accessing this informaiton the background process will fill the result in that table. YOu need to clean up this table after certain time.

    Hope this will help.

    -Shiv