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.

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

With 40ms ping common sense is that it will take a bit more to fetch data. So let's say 50ms to fetch one record. With 45.000 records multiplied with 50ms per record it comes to total of 2.250.000 ms = 2.250 sec = 37.5 minutes. Which matches your 'half an hour' description. Same calculation for 5000 records comes to 4.1 minute.

First thing that I would do is to place print "Point X at: " . time(); on a couple of places (change X with 1, 2, 3 ... so you know what point in code it is) and try to figure out what exactly is going on - kind of simple code profiling. Just to make sure something isn't wrong in the prepare() stage. Though it looks like you already checked that.

Second thing would be to actually not fetch data that you don't need (as someone wrote, filter those that you don't need with something like WHERE whatever IS NOT NULL - or whatever you are doing in get_data and Oracle/PlSQL uses) and not bind $valreal and $colorder that I don't see you are using.

Actually I wouldn't bind the things at all - I would just do my $raResults = $sth2->fetchall_arrayref(); which will give you back exactly the same data structure you are building in that while(fetch){} loop (though depends on what get_data is returning) and it should fetch data much faster then one by one. If you need to do some transformation with each row (which I don't see you are doing) - just do it in the get_data itself.

---

Have you tried freelancing/outsourcing? Check out Scriptlance - I work there since 2003. For more info about Scriptlance and freelancing in general check out my home node.

If you don't want the rows returned, filter them in your SQL. I don't understand how PL/SQL works, so I can't help you there, but likely you can turn your cursor into a view and then select only these rows from it where your "interesting" column is not null.

Both Corion and Techcode are true, but you can choose something between...

If you cannot, for some reasons, filter the data by SQL, you can use repeatedly $sth->fetchall_arrayref($slice, $max_rows) It allows to fetch, for instance, maximally 10000 rows at the same time and to not exhaust memory on the client side, when unpredicted amount of rows has to be returned.