Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

Re: fastest method to use DBI

by JavaFan (Canon)
on Jul 07, 2009 at 09:54 UTC ( #777827=note: print w/replies, xml ) Need Help??

in reply to fastest method to use DBI

Without knowing which driver you are using, most certainly not. The way you are doing it duplicates data - you are using bind variables and you are using fetchall_arrayref. Furthermore, you first fetch every row in a big table, then you loop over it (in a, IMO, weird way) and slightly rearrange it. You're doing quite a lot of data duplications, even before you do anything with the data.

First you have to answer the question: do I need all 100000 rows before I do any processing? Or do you want to process each row? In which case you're (probably) better off fetching a row at the time, doing the processing, then fetching the next. Note I say probably - if the processing takes a long time, you are holding resources (perhaps even locks) in the database which may influence other processes accessing the data.

AFAIK, bind variables and fetchrow_arrayref are the fastests way to retrieve data - with bind variables probably the fastest (but I haven't benchmarked it myself, and it may vary between drivers). I never use bind variables, as I don't like its action at a distance, but if fetching was the bottleneck of a time critical program, I'd certainly look into it.

Replies are listed 'Best First'.
Re^2: fastest method to use DBI
by dsheroh (Monsignor) on Jul 07, 2009 at 14:28 UTC
    Yes, bind_columns/->fetchrow_arrayref (or its alias, ->fetch) is the fastest way to retrieve data, per the DBI docs:
    [fetchrow_arrayref] Fetches the next row of data and returns a reference to an array holding the field values. Null fields are returned as undef values in the array. This is the fastest way to fetch data, particularly if used with $sth->bind_columns.
    Perhaps the OP misread this as saying that the fastest option was fetchall_arrayref rather than fetchrow_arrayref?

    The recommended technique, then, would be:

    my $sth = $dbn->prepare("select number,id,start_dat,end_dat from SUBSC +RIBERSLIST"); $sth->execute(); $sth->bind_columns(\$number, \$id, \$start_dat, \$end_dat); my %hash =(); while ($sth->fetch) { # ->fetch populates the variables from ->bind_columns push @{$hash{$number}}, [$id, $start_dat, $end_dat]; }; $sth->finish(); $dbn->disconnect;

    But, as already noted, you should also SELECT only the rows you need and do your processing line-by-line instead of sucking in the whole table at once if possible. I've only addressed the mechanics of how the OP is pulling the SELECTed rows.

      I suspect what the OP saw was something about fetchall_arrayref bering the fastest way to fetch all the data, which is true since it doesn't require looping in perl. However, fetchall_arrayref doesn't work with bind_columns. Also, since the OP is only after a single row, there is no advantage to fetchall_arrayref.
        which is true since it doesn't require looping in perl

        Actually DBI implements fetchall_arrayref as loop in perl

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://777827]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2020-10-28 10:07 GMT
Find Nodes?
    Voting Booth?
    My favourite web site is:

    Results (260 votes). Check out past polls.