Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re^3: latency and round-trips

by techcode (Hermit)
on Dec 17, 2008 at 13:07 UTC ( #730917=note: print w/replies, xml ) Need Help??

in reply to Re^2: latency and round-trips
in thread DBI Queries take half an hour

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.

Replies are listed 'Best First'.
Re^4: latency and round-trips
by joec_ (Scribe) on Dec 17, 2008 at 14:25 UTC

    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?

      Have you tried using Google?

      Or just go and read the DBI documentation directly:

      DBI or type perldoc DBI at your command prompt.

        Thanks for that insight. Refering back to my post above, "will fetchall_arrayref affect clobs?".... I have tried the following code:
        sub get_all_data{ my $max_rows = 5000; while (my $aref = $sth2->fetchall_arrayref(undef, $max_rows)) { $struct = $dbh_ideas->ora_lob_read(@$aref[1],1,65536); push @$raResults, [ $aref[0],$struct ]; }; return $raResults; }

        Then later:

        $p = Project->new_project($id); $cursor = $p->get_all_data; foreach my $row (@$cursor){ print OUTPUT do{ local $" = qq{\t}; qq{$row->[1]\$\$\$\$\n}; } if defined $row->[1]; }

        My output file doesnt get written, but equally doesnt die either. I get a "locator is not of type OCILobLocatorPtr at line 146." error message. It is something to do with ora_lob_read - does anyone know the syntax for this situation? Thanks

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://730917]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (7)
As of 2017-11-24 17:46 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (351 votes). Check out past polls.