• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Fetching nK records in batch from client side?

 
Mikael Bellec
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 65129
92
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mikael Bellec
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Mikael Bellec
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic