aspose file tools*
The moose likes JDBC and the fly likes Which approach is better ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Which approach is better ? " Watch "Which approach is better ? " New topic
Author

Which approach is better ?

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
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

Joined: May 26, 2003
Posts: 30919
    
158

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
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

Joined: May 26, 2003
Posts: 30919
    
158

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

Joined: Jun 08, 2006
Posts: 140
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

Joined: May 26, 2003
Posts: 30919
    
158

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

Joined: Jun 08, 2006
Posts: 140
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Which approach is better ?