DBI Queries take half an hour

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

Hi,

I have some code which should return ~5000 rows (2 columns) (as a reference to an array) from an Oracle database. To execute this PLSQL query takes ~1 second in TOAD or SQLDeveloper however in Perl, using the DBI and DBD::Oracle drivers, it takes just over half an hour. As far as I know, bind_columns is the most efficient way of retrieving data, but maybe you guys could suggest other ideas?

my $plsql=<<ENDPLSQL;
    DECLARE
  l_header pacProject.data_header_cur;
  l_data   pacProject.data_cur;
  l_project_id NUMBER;
  l_design_set_id NUMBER;
  l_layout_id NUMBER;
BEGIN
  l_design_set_id := NULL;
  l_layout_id := 1;
  pacProject.get_data(
    :l_project_id,
    l_design_set_id, 
    l_layout_id,
    l_header,
    :l_data);
END;
ENDPLSQL
    my $sth_ideas = $dbh_ideas->prepare($plsql);
    my $sth2;
    my @row;
    $sth_ideas->bind_param(":l_project_id",$self->{project_id});
    $sth_ideas->bind_param_inout(":l_data",\$sth2,0,{ora_type => ORA_R
+SET});
    $sth_ideas->execute;
    my $raRow;
    my $raResults = [];
    my ($id, $valreal, $valtext, $colorder);
    $sth2->bind_columns(\($id, $valreal, $valtext, $colorder));
    while( $sth2->fetch )
    {
        
        push @$raResults, [ $id,$valtext ];
    }
    return $raResults;
[download]

Thanks.
Joe.

Comment on DBI Queries take half an hour Download Code

Replies are listed 'Best First'.
Re: DBI Queries take half an hour by mje (Curate) on Dec 16, 2008 at 10:53 UTC
Asked last week in http://www.perlmonks.org/?node_id=727389	[reply]
Re^2: DBI Queries take half an hour by joec_ (Scribe) on Dec 16, 2008 at 11:00 UTC
Thanks for pointing that out, i have taken all of the considerations in hand and will give them a go.	[reply]
Re^3: DBI Queries take half an hour by marto (Cardinal) on Dec 16, 2008 at 11:03 UTC
Given that it was you who posted that question, I'd have thought you'd have already been aware of the replies. Martin	[reply]
Re: DBI Queries take half an hour by tirwhan (Abbot) on Dec 16, 2008 at 10:59 UTC
You asked that question before. Which of the answers did you not understand? What did you try? All dogma is stupid.	[reply] [d/l]
Re^2: DBI Queries take half an hour by joec_ (Scribe) on Dec 16, 2008 at 11:23 UTC
I tried everything - from setting rowcachesize and using bind_columns and reading the DBD::Oracle documentation, but none of it made any difference. This is a seperate question as i am returning rows in a different way. The execute methods take a second which is fine, it is the fetch method that is taking the time. Thanks for the help.	[reply]
Re^3: DBI Queries take half an hour by runrig (Abbot) on Dec 16, 2008 at 20:33 UTC
Are there indexes? Do you have permission to use the indexes? Did you get a query plan? Do TOAD and perl/DBI have the same query plan?	[reply]
Re: DBI Queries take half an hour by moritz (Cardinal) on Dec 16, 2008 at 10:47 UTC
We had a very similar question last week, maybe use Super Search to find it, and get inspired by the discussion?	[reply]
latency and round-trips by tallfred (Acolyte) on Dec 16, 2008 at 20:56 UTC
What is the latency (ping time) between the database server and the machine running perl? Your perl code seems to fetch one row at a time, and perrin's suggestion of adjusting the pre-fetch should help.	[reply]
Re: latency and round-trips by Anonymous Monk on Dec 17, 2008 at 09:23 UTC
Hi, The average ping time is 40 ms. I tried increasing the pre-fetch using `$dbh_ideas->{RowCacheSize}=100`, but with no effect. Is that a sensible number? or would you suggest something different? My situation is one where i only have access to the pl/sql procedures i am given - i cannot query plan or add indexes without going through the proper channels. Thanks for your time.	[reply] [d/l]
Re^2: latency and round-trips by joec_ (Scribe) on Dec 17, 2008 at 09:31 UTC
Update: Sorry, i forgot to login. I have just discovered that this procedure is actually returning close to 45000 rows. Now the time doesnt seem so unreasonable. Again, the situation is this: Although 45000 are now returned, only 5000 of them have values in the column that i need to extract. I now wonder does DBI have a "fetch_if_not_null" method or some such mechanism? I could always put `while ($row=$sth->fetchrow_arrayref){ print $row if defined $row; }` [download] But this obviously doesnt get rid of the time taken to fetch the data, only doesnt display the null DB fields. End Update	[reply] [d/l]
Re^3: latency and round-trips by Corion (Patriarch) on Dec 17, 2008 at 10:07 UTC
Re^3: latency and round-trips by techcode (Hermit) on Dec 17, 2008 at 13:07 UTC
Re^4: latency and round-trips by joec_ (Scribe) on Dec 17, 2008 at 14:25 UTC
Some notes below your chosen depth have not been shown here
Re^3: latency and round-trips by pajout (Curate) on Dec 17, 2008 at 15:38 UTC
Re^4: latency and round-trips by joec_ (Scribe) on Dec 17, 2008 at 23:55 UTC
Some notes below your chosen depth have not been shown here
Re: DBI Queries take half an hour by Anonymous Monk on Dec 16, 2008 at 11:53 UTC
hi try by inserting indexes for both the columns that you are fetching so that it will fetch the data's from data base quickly	[reply]