This week's book giveaway is in the Java in General forum.
We're giving away four copies of Think Java: How to Think Like a Computer Scientist and have Allen B. Downey & Chris Mayfield on-line!
See this thread for details.
Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Which approach is better ?

 
Atul Mishra
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all, [Modifying the question after reading "Pagination" FAQ in Javaranch]

I have a dynamic report creation requirement. The report can have upto 150000 records. The report is just a Select query[querying 13 fields in the production table instance].
This Select query may be running say once/twice per day. So what paging technique is best here ?
-Query based
- Cache based ?
Thanks
[ June 20, 2007: Message edited by: Atul Mishra ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34375
345
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Atul,
What benefit would you expect to get from a stored procedure? Typically, stored procs save on network traffic. This doesn't apply for a single query.

I would go with a select query, preferably with some sort of batching so you don't hit a timeout.
 
Atul Mishra
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne,

Thanks for your reply.

Could you please elaborate on this:

I would go with a select query, preferably with some sort of batching so you don't hit a timeout.


When you say batching, what kind of batching ?
Please give me some suggestions, Thanks.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34375
345
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Atul Mishra:
When you say batching, what kind of batching ?

I mean breaking down the query into smaller chunks.

How it works depends on your query. Say you are querying a million accounts. You could return them in sets of 100.
 
Atul Mishra
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne,

So I believe you a talking about query based paging, right ?

I am using Oracle as my database.

If I have 70000 records, whats a good performance time for returning all 70000 if I return them as batches of 5000..

Thanks,
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34375
345
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Atul,
The total time is about the time and depends on your database and network speed. The point of batching is to avoid a timeout.

Suppose it takes 6 minutes to return all the data and your network has a two minute timeout. This wouldn't work. If you returned the data in four batches, it would work.
 
Atul Mishra
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeaane,

I am opening a new thread here to get opinions for constructing the Query for pagination.

the query should return xxx nof results[as batches]

Thanks
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic