Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Recommeded way to delivers million rows to a client  RSS feed

 
Anne Forumer
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is a curiosity question but, I'm sure, has been implemented many times.

If a query from a web client results in very large number (million) of rows of data, what are some of the preferred ways to deliver them to the client? The client may decide to see none to all of the data? Should the data be delivered to client in pages in a piecemeal fashion or all at once? How should the database access be configured for such a scenario?

Thanks

Anne
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first step is determining which queries might result in large result sets. The second step is to alter those queries so they don't and instead use pagination directly in the query. The reason is that loading that much data to the application server is going to kill the application just as sending a million rows to the client will.

There are specific tricks for doing pagination in the database, but if you application has enough users you may want to use more intelligent tricks. The simple one does searching, sorting, and pagination in a single nested query with two inner queries.

Briefly, the innermost query performs the search and sort. The query wrapping that adds the rownum column and cuts off the upper bound. The outermost query cuts off the lower bound.

Here's an example that returns page P of all science fiction books, each page being 25 books.Note the need to alias rownum to use it in the outermost where clause. You can find a good discussion of this technique on Oracle's Ask Tom site.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!