Hi,

My system has 4 GB RAM, 250GB HDD. Debian etch OS, PostgreSQL 8.1, perl 5.8.8, perl DBI 1.52
my query may return more than 3000000 records and it is build upon three tables using multiple joins and conditions.
when i execute this query from perl program(which uses perl DBI), it is taking more time to return the records, and when it returns the memory is completely eaten up by the perl process.

so I decided to get the records patch by patch continuously. a patch is basically 1000.

I did a vast search on the net and found the following methods were useful

- using bind_columns (but more method calls are needed).
- using offset and limit in my query itself, (but internally the server may get all the records and move to the offset then return to us).
- using cursors of postgreSQL.( I don't know much about its efficiency).
I am confused now, what to use among the above choices?,
Does any other better ways exist? If so, please help me to get out of this problem.

In reply to What are effective methods for retriving many number of rows from postgreSQL tables using perl DBI? by targetsmart

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.