in reply to Query takes 895ms in TOAD but 42s in DBI

I don't know what this returns, but my guess is that it has to do with buffering of rows. You can control how much DBD::Oracle fetches at once. The best setting depends on how you're using the data, e.g. you may want to get it all at once for faster fetching or get it a few rows at a time to avoid filling up RAM.
  • Comment on Re: Query takes 895ms in TOAD but 42s in DBI

Replies are listed 'Best First'.
Re^2: Query takes 895ms in TOAD but 42s in DBI
by joec_ (Scribe) on Dec 03, 2008 at 10:49 UTC
    ok, the actual execute() method takes 1 second, it is the fetchrow_array() that is taking the time. How would i go about changing the default DBD::Oracle settings?

    TIA - Joe

[reply]
[reply]
      I don't know what settings the DBD::Oracle module provides but I have seen a mention of RowCache in DBI that you could give a go. Also it would be interesting to test the speed of the different forms of fetch like the bind_columns variant for example : 
      $sth_dipp = $dbh_ideas->prepare(q{DECLARE dipp PacProject.dipp_project
+_cur; BEGIN pacproject.get_dipp_projects(:dipp); END;});
$sth_dipp->bind_param_inout(":dipp",\$s_dipp_proj,0,{ora_type => ORA_R
+SET});
$sth_dipp->execute;

my ($name, $code, $RA, $manager);
$sth_dipp->bind_columns(\($name, $code, $RA, $manager));


while ( $sth_dipp->fetch ){
    $name ||= 'Unknown';
    $code ||= 'Unknown';
    $RA ||= 'Unknown';
    $manager ||= 'Unknown';

    print qq|$name,$code,$RA,"$manager"\n|;    
}

      [download]
[reply]
[d/l]
        The bind_columns approach is the fastest. You can see benchmarks on some of this here.
[reply]

In Section Seekers of Perl Wisdom