• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Fetching nK records in batch from client side?

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 67750
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
I didn't say it. I'm just telling you what this tiny ad said.
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic