atemerev:

Usually, large resultsets scream "you're not using the server enough!". You may want to look over the operators that SQL offers you before you just use PostgreSQL as a data server. Since it's iterating over the records anyway, it's usually a good tradeoff to have the server do your aggregation work as well, so you can (hopefully) return smaller datasets.

Remember, with today's CPUs being so blindingly fast, you can do an awful lot of calculation in the same amount of time you can transmit a packet over the network. Obviously, I don't know just what sorts of operations you're performing on your data, so it may be inapplicable to your situation.

For example, if you're interested in the low, high and average quotes for various stocks on a selection of days, you could use:

select   TickerName, convert(char(8),quoteDate,112) quoteDate, 
         min(quote), max(quote), avg(quote)
from     quotes
where    quoteDate between @startPeriod and @endPeriod
group by TickerName, convert(char(8),when,112)
order by TickerName, convert(char(8),when,112)

[download]

So if you have a dozen quotes a day for each of the stocks of interest, this query would return far less data than if you would retrieve all the detail and compute the min/max/average in perl.

...roboticus

In reply to Re: PostgreSQL cursors with Perl. by roboticus
in thread PostgreSQL cursors with Perl. by atemerev

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.