Re: Performance issues when the number of records are around 10 Million

[venu madhav <venutaurus539@xxxxxxxxx>]

On Wed, May 12, 2010 at 7:26 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:

venu madhav <venutaurus539@xxxxxxxxx> wrote:

>> > If the records are more in the interval,
>>
>> How do you know that before you run your query?
>>

> I calculate the count first.

This and other comments suggest that the data is totally static
while this application is running.  Is that correct?

[Venu] No, the data gets added when the application is running. As I've mentioned before it could be as faster as 100-400 records per second. And it is an important application which will be running 24/7. 

> If generate all the pages at once, to retrieve all the 10 M
> records at once, it would take much longer time

Are you sure of that?  It seems to me that it's going to read all
ten million rows once for the count and again for the offset.  It
might actually be faster to pass them just once and build the pages.

[Venu] Even if the retrieval is faster, the client which is viewing the database and the server where the data gets logged can be any where on the globe. So, it is not feasible to get all the 1 or 10 M records at once from the server to client.
 

Also, you didn't address the issue of storing enough information on
the page to read off either edge in the desired sequence with just a
LIMIT and no offset.  "Last page" or "page up" would need to reverse
the direction on the ORDER BY.  This would be very fast if you have
appropriate indexes.  Your current technique can never be made very
fast.

[Venu] I actually didn't understand what did you mean when you said "storing enough information on the page to read off either edge in the desired sequence with just a
LIMIT and no offset". What kind of information can we store to improve the performance.  Reversing the order by is one thing, I am trying to figure out how fast it is. Thanks a lot for this suggestion.

Thank you,
Venu.

-Kevin