For anyone still following this thread, I think the point is now rather
moot.
Using Perl, I tried variations of fork & wait with no noticeable speed
performance.
Determined to try threads, I busted out the Ruby Pick-Axe book and created a
threaded version. Still no performance improvement.
It now seems clear to me that, whatever I may come up with on the client
side the Oracle Server is going to handle its own way regardless.
Looks like nothing is faster than ye old "sqlplus blah/blah@music_teleblah <
sqlfile.txt".
Sigh...
Thanks for all the help & suggestions. At least I know more about shared
memory than I ever did before. :)
Regards,
Dan
On Fri, 25 May 2001 08:37:37 -0500, Mr. Sunray said:
> Hi all,
>
> Perl 5.6 on bughat 6.2, using DBI 1.14, DBD-Oracle 1.06.
>
> Ok - I thought I'd try and optimize a series of sql SELECT statements by
>
> forking each one and storing the results in a shared memory object.
>
> Here's the code - I'm reading a series of sql select statements from a
> file, fork'ing a child for each one, executing the 'fetch' within the
> child and trying to store the results in an array for later retrieval.
>
> The problem seems to be with the shared memory objects. Either the
> array of results grows each time I run the program or else I get "Can't
> use an undefined value as an ARRAY reference at
> /usr/local/lib/perl5/site_perl/5.6.0/IPC/Shareable.pm line 446, <GEN1>
> chunk whatever".
>
> Now, I've been told that part of the problem (at least) is that I'm
> trying
> to store references, which probably won't work (though it seems to
> work sometimes).
>
> I guess my big question is how can I use fork to speed up my queries
> *and*
> store the results in the parent (without using a text file)? Should I
> be
> looking at threads instead? Is there a *standard idiom*?
>
> Thanks in advance for any help.
>
> WARNING - be prepared to use 'ipcs' and 'ipcrm' if you want to play with
>
> this script! You may have stray memory objects floating around
> afterwards.
>
> Be sure to run this/your program at least twice to make sure your shared
>
> memory objects aren't "collecting".
>
> #!/usr/local/bin/perl -w
>
> use strict;
> use DBI;
> use DBD::Oracle;
> use IPC::Shareable;
> use IO::File;
> use POSIX qw(WNOHANG);
>
> use constant DB => 'myDB';
> use constant USER => 'user';
> use constant PASSWD => 'passwd';
> use constant DRIVER => 'Oracle';
>
> #+++++++++++++++++++++++++++++++++++++++++++++
> # File contains 4 sql SELECT statements.
> # Use "select sysdate from dual" if you like, so long as it works.
> #+++++++++++++++++++++++++++++++++++++++++++++
> use constant SQLFILE => 'file.sql';
>
> # Child handler
> $SIG{CHLD} = \&reapChild;
>
> my($dbh, $sth, @results, @allRecords);
> my $if = IO::File->new(SQLFILE) or die "Couldn't open file: $!\n";
>
> #+++++++++++++++++++++++++++++++++++++++++++++++++
> # I am not entirely sure what the proper settings for these should be.
> # I've tried various combinations with no (or bad) luck.
> #+++++++++++++++++++++++++++++++++++++++++++++++++
> my $glue = 'data';
> my %options = (create => 'yes', exclusive => 0, destroy=>1)
> tie @allRecords, 'IPC::Shareable', $glue, { %options } or die "Tie
> failed: $!\n";
>
> $/ = ""; # We're going to read each 'select' statement as an input
> record
> while(my $query = <$if>){
> chomp($query);
>
> my $child = fork();
> die "Can't fork: $!\n" unless defined $child;
>
> if($child == 0){
>
> # Not sure if I need 'InactiveDestroy' here or not
> $dbh = DBI->connect(DB,USER,PASSWD,DRIVER,
> {RaiseError=>1,PrintError=>1,InactiveDestroy=>1}
> );
>
> $sth = $dbh->prepare($query);
> $sth->execute();
> while( my $hash_ref = $sth->fetchrow_hashref() ){
> push(@results, $hash_ref);
> }
>
> #+++++++++++++++++++++++++++++++++++++++++++++
> # '@allRecords' should contain a reference to an array of hash
> # reference for each select statement (thus, four total).
> #+++++++++++++++++++++++++++++++++++++++++++++
> push(@allRecords, \@results);
>
> $sth->finish();
> $dbh->disconnect();
> exit 0;
> }
> }
>
> close($if) or die "Unable to close input file: $!\n";
>
> #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> # I expect to see four array references here. When I try this, I either
>
> # get "Can't use undefined value as an array reference" warnings
> # from the 'Shareable.pm' module or the list keeps growing and growing
> # each time I run the program. What do I need to do?
> #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> foreach my $ref(@allRecords){
> print "Ref is: $ref\n";
> }
>
> sub reapChild{ do {} while waitpid(-1, WNOHANG) > 0 }
>
>
>
>
>
>
--
Michael Wray
Network Administrator
FamilyConnect, Inc.
