Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

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

by targetsmart (Curate)
on Oct 13, 2007 at 08:12 UTC ( #644611=perlquestion: print w/ replies, xml ) Need Help??
targetsmart has asked for the wisdom of the Perl Monks concerning the following question:

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.

Comment on What are effective methods for retriving many number of rows from postgreSQL tables using perl DBI?
Re: What are effective methods for retriving many number of rows from postgreSQL tables using perl DBI?
by moritz (Cardinal) on Oct 13, 2007 at 10:06 UTC
    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.

Re: What are effective methods for retriving many number of rows from postgreSQL tables using perl DBI?
by duff (Vicar) on Oct 13, 2007 at 13:46 UTC

    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.

Re: What are effective methods for retriving many number of rows from postgreSQL tables using perl DBI?
by perrin (Chancellor) on Oct 13, 2007 at 15:15 UTC
    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.
Re: What are effective methods for retriving many number of rows from postgreSQL tables using perl DBI?
by Cop on Oct 13, 2007 at 16:13 UTC

    Okay, Perl has bad reputation for memory usage, but you are doing something stupid. Doesn't matter what language you use, you are doing the wrong thing.

    take a look at your overall design and ask the question: why am I doing this? Can I do something different that makes more sense?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://644611]
Approved by g0n
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (5)
As of 2014-09-20 22:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (163 votes), past polls