Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Query takes 895ms in TOAD but 42s in DBI

by joec_ (Scribe)
on Dec 02, 2008 at 12:02 UTC ( [id://727389] : perlquestion . print w/replies, xml ) Need Help??

joec_ has asked for the wisdom of the Perl Monks concerning the following question:


I have a PL/SQL function, which returns a cursor of 1100 rows. In TOAD this takes 895ms, if i implement this in DBI with DBD::Oracle it takes 42 seconds. Would there be any particular reason for this or is it just the DBI implementation?

Here is my code:

$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; while (@projects=$s_dipp_proj->fetchrow_array){ $name = $projects[1]; $code = $projects[2]; $RA = $projects[4]; $manager = $projects[7]; if (defined($manager) && defined($name) && defined($code) && defin +ed($RA)){ $csv = $csv . "$name,$code,$RA,\"$manager\"\n"; }else{ if (not defined($name)){$name="Unknown";} if (not defined($code)){$code="Unknown";} if (not defined($RA)){$RA="Unknown";} if (not defined($manager)){$manager="Unknown";} $csv = $csv . "$name,$code,$RA,\"$manager\"\n"; } } print $csv;



Replies are listed 'Best First'.
Re: Query takes 895ms in TOAD but 42s in DBI
by mje (Curate) on Dec 02, 2008 at 15:33 UTC

    I don't think you are comparing like with like here.

    Outputting data to the terminal is never a good test of speed. Try it without the print to take this out of the equation.

    Also, why bother doing all those tests of NULL to change the returned value to Unknown when you can use coalesce in your SQL select and let oracle do it.

      Thanks for the replies. I will get round to profiling it when i can get the module installed.

      I cant use coalesce as i dont have access to the schema. Even just doing the query without printing to stdout or files takes 42 seconds.

      regards, Joe

        COALESCE is a function you put into your query that takes the first non-NULL value.

        As for what's going on ... You said this returns a cursor. What happens in TOAD if you subsequently run through all the rows in the cursor? I suspect that DBD::Oracle is doing just that while TOAD isn't.

        My criteria for good software:
        1. Does it work?
        2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
Re: Query takes 895ms in TOAD but 42s in DBI
by moritz (Cardinal) on Dec 02, 2008 at 13:45 UTC
    Try profiling it, for example with Devel::NYTProf, and see where the time is spent, maybe that will give you some answers.
Re: Query takes 895ms in TOAD but 42s in DBI
by perrin (Chancellor) on Dec 02, 2008 at 21:41 UTC
    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.
      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

        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|; }
Re: Query takes 895ms in TOAD but 42s in DBI
by runrig (Abbot) on Dec 12, 2008 at 00:05 UTC
    Perhaps you are running the same thing in the two environments, but as different users, and one user does not have permission to use an index?
Re: Query takes 895ms in TOAD but 42s in DBI
by runrig (Abbot) on Dec 04, 2008 at 16:59 UTC
    I don't know the reason for the slowness, as 1100 rows isn't that much, and fetchrow_array() vs. bind_columns()/fetch() (or bind_col) doesn't explain that much of a difference (is perl/DBI and TOAD run from the same machine?). But why do you concatenate to the $csv string and only print at the end rather than just printing as you go (not that that explains any speed difference either...are you seeing all 1100 rows in TOAD, or just the first 20 or so)?