Query takes 895ms in TOAD but 42s in DBI

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

Hi,

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;
[download]

TIA

Joe

Comment on Query takes 895ms in TOAD but 42s in DBI Download Code

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.	[reply]
Re^2: Query takes 895ms in TOAD but 42s in DBI by joec_ (Scribe) on Dec 02, 2008 at 16:31 UTC
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	[reply]
Re^3: Query takes 895ms in TOAD but 42s in DBI by dragonchild (Archbishop) on Dec 02, 2008 at 22:38 UTC
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: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
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.	[reply]
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.	[reply]
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]
Re^3: Query takes 895ms in TOAD but 42s in DBI by marto (Cardinal) on Dec 03, 2008 at 10:56 UTC
Hi joec_, Take a look at Connecting to Oracle II from the DBD::Oracle documentation, paying attention to the various attributes you can set. Hope this helps Martin	[reply]
Re^3: Query takes 895ms in TOAD but 42s in DBI by kubrat (Scribe) on Dec 03, 2008 at 15:14 UTC
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]
Re^4: Query takes 895ms in TOAD but 42s in DBI by perrin (Chancellor) on Dec 03, 2008 at 16:22 UTC
Re^5: Query takes 895ms in TOAD but 42s in DBI by joec_ (Scribe) on Dec 04, 2008 at 10:17 UTC
Some notes below your chosen depth have not been shown here
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?	[reply]
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)?	[reply] [d/l]


We don't bite newbies here... much
	PerlMonks