This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Fetching  nK records in batch from client side? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Fetching  nK records in batch from client side?" Watch "Fetching  nK records in batch from client side?" New topic
Author

Fetching nK records in batch from client side?

Mikael Bellec
Greenhorn

Joined: Nov 27, 2001
Posts: 6
Hi, I have an application that can fetch a few thousand records from a few million records table.
The query is build dynamicaly and tuned to the near max (indexes and so on). Handling the records on the client side is not an issue.
The bottleneck when this scenario occurs is the execution time of this query which is exponential with the number of records returned.

The only thing I could think of to improve the situation would be to
a) archive and reduce the size of the table
b) find a way to fetch some of the records while the query is still executing.

Now I am interested in finding out if b) is possible. Oracle permits the use of /* +first_rows */ hint that indicate the optimizer to return N rows in priority.
Some tools like Toad use this to display N records while the query is still executing.
Has anyone any experience on how to implement similar behavior on the client side (i.e. result set is not completly populated but allows to get the records available)?

Thanks,
Mikael.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60785
    
  65

What DB are you using? PostgreSQL, for example, has the LIMIT and OFFSET keywords that allow you to specify a subset range of records within the full set of results to fetch.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Mikael Bellec
Greenhorn

Joined: Nov 27, 2001
Posts: 6
I am using Oracle 8.
I know of /* first_rows */ hint which encourages the optimizer to bring back N records first. I am not sure how to take advantage of this on the client side though.

Mikael.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Mikael Bellec:
The query is build dynamicaly and tuned to the near max


can you explain this how are you building dynamic queries.

also confirm that if you are using oracle 8.1.5 or oracle 8.1.7

if you are using oracle 8.1.7 ie Oracle 8i then you can opt for materialized view, rather archiving data


Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Mikael Bellec
Greenhorn

Joined: Nov 27, 2001
Posts: 6
The queries are built using an in house framework where web screens allow to choose the columns you need displaying. According to this selection, we parse some xml to indicate how to build the queries and make the join between the different tables.

The queries are quite efficient and the main bottleneck resides in joining 2 tables which have a couple of million records.

We use Oracle 8i.
How could a materialised view help with this?

Tx, Mikael.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Mikael Bellec:
The queries are built using an in house framework where web screens allow to choose the columns you need displaying. According to this selection, we parse some xml to indicate how to build the queries and make the join between the different tables.

The queries are quite efficient and the main bottleneck resides in joining 2 tables which have a couple of million records.

We use Oracle 8i.
How could a materialised view help with this?

Tx, Mikael.


Are you parsing your xml based on user selection or parsing at a time.

some times back I had worked on similar requiremet where every thing was configurable like tables, columns of table, also user was able to select the number of columns to display. The login I used there was I cached all nodes and element based on their tag name etc so I avoided parsing xml every time

Only advice to you is make every possible join.


the materialised view which I mentioned was releated to your archive issue.
I was suggesting rather removing data from table have a materiasied view and refresh it preodically on the same criteria. so you will always have your database integrity maintained.and since your M-view would have less data so access of data would be fast.


Shailesh
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Fetching nK records in batch from client side?
 
Similar Threads
How to tell which port mySQL is running on?
Statement.setMaxRows
Fetch only 'n' records from the ResultSet
Prepared statement
dynamically add rows to the exitsting table