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]

End Update

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.

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.

Thanks

Please could you show me how to use $sth2->fetchall_arrayref or point me in the direction of a tutorial / docs. Also, my DBA has changed the table now, so i have an id column and a clob column, will this affect fetchall_arrayref - how would i do this?

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.

Ok, i have got my DBA to filter the SQL now. I have a table with 5000 rows (clobs).

Is this the correct way to dereference an array reference? $struct=@$aref[1] or should i do $struct=@$aref->[1]

As it currently stands my code above takes 7 minutes, with the occasional "not of type ocilobptr" warning, which i assume just means one of my fields is null, which i can check for later. Is 7 minutes something that sounds reasonable? According to the docs, fetchall_arrayref isnt necessarily faster than fetchrow_array.

I would appreciate your opinions on this - which would you say is better for 5000 clobs? can you suggest a suitable rowcachesize for fetchrow_arrayref?

I know i have bugged many of you but i really do appreciate your help. Thanks.