This week's book giveaway is in the General Computing forum. We're giving away four copies of Arduino in Action and have Martin Evans, Joshua Noble, and Jordan Hochenbaum on-line! See this thread for details.
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)?
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.
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.
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
posted
0
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?
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.
0
