What are effective methods for retriving many number of rows from postgreSQL tables using perl DBI?

[targetsmart]

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.

[moritz]

First of all the more method calls for bind_columns shouldn't be a problem.

You should generally use placeholders and bind parameters. When you call prepare, a stored procedure is created internally and reused each time you call execute on that handle - which is more efficient than calling do($statement) mutlitple times.

You should just try a query with limit and offset to retrieve the data patch by patch, and try a query that returns them all and transfer them row by with selectrow_arrayref or selectrow_array. Then benchmark to find out which one is faster.

[perrin]

As the docs say, "the execute method fetches all data at once into data structures located in the front-end application. This approach must to be considered when selecting large amounts of data!" I think you will have to use cursors if you want to get around this problem.

[duff]

Depending on how many columns in your result set, and how complicated your query, 3,000,000 records shouldn't be that big of a deal. You really should show us your perl code, your SQL, and your database schema as you could be doing something strange that's eating your memory and we would never know it. For instance, if you load the entire result set into a perl array, that might not be the best thing for memory consumption. Another for instance, if you're doing a 5 table join on all columns but are only using a few columns from the result set, that too might not be the best thing.