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

On Wed, May 12, 2010 at 7:26 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> venu madhav <venutaurus539(at)gmail(dot)com> 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
>