If the query just hangs there in mysql, putting it into DBI is not going to fix
that. Are the columns indexed in the tables? If not, you've probably got it
doing nested full table scans. Try adding indexes to the tables on the join
columns.
Get the query to run in mysql before you ever try it in perl again, or if there
is anything wrong with your perl code, you'll never know it.
jeff
"Hastie, Christa" wrote:
> Thanks Marcelo for all three of those points.
> I tried performing just the query from mysql, but it just hangs there also,
> even if I leave it for an hour!
> I've modified the code to stop opening and closing the file in the loop, and
> added the lock values, but it's still just running and running and still
> produces no results....hmmmm:
>
> #!/usr/local/bin/perl
>
> use DBI;
> use strict;
>
> my($dbh);
> my($sth);
> my($exclusive_lock) = 2;
> my($unlock_lock) = 8;
>
> #eval { $dbh = DBI->connect("DBI:mysql:dbname;host=localhost", "dbuser",
> "dbpassword", {'RaiseError' => 1}); };
> if($@) {
> print "Content-type: text/html\n\n";
> my($error) = "Error opening Database: $@\n";
> print "<html><body>$error</body></html>";
> }
>
> my $sth = $dbh->prepare("SELECT users_old.UserId, users_old.Email FROM
> users_new, users_old WHERE users_old.Email = users_new.Email");
> $sth->execute or die "Unable to execute query: $dbh->errstr\n";
> my ($row);
>
> my($data_log) = "/home/chastie/sony_showbiz.txt";
> open (DATATEMP, ">>$data_log");
> flock (DATATEMP, $exclusive_lock);
>
> while($row = $sth->fetchrow_arrayref) {
> print LOG "$row->[0]\t$row->[1]\n" or die "Error: $!\n";
> }
>
> flock (DATATEMP, $unlock_lock);
> close (LOG);
>
> $sth->finish;
> $dbh->disconnect;
> exit;
>
> -----Original Message-----
> From: Marcelo Guelfi [mailto:[EMAIL PROTECTED]]
> Sent: Friday, November 02, 2001 11:34 AM
> To: [EMAIL PROTECTED]
> Subject: Re: ::massive sql query using dbi - please help::
>
> 1) Did you try to perform that query from mysql instead of a perl program
> to measure the time?
> 2) Is this code correct? :
> open (DATATEMP, ">>$data_log");
> flock (DATATEMP, $exclusive_lock);
> print LOG "$row->[0]\t$row->[1]\n";
> close (LOG);
>
> You never opened de LOG descriptor so you can't write to it. Add a warn or
> die call to capture the error:
> print LOG " " or die "Error: $!\n";
>
> 3) Why are you opening,locking and closing the file inside the loop?
>
> Saludos,
> Marcelo.
>
> Saludos,
> Marcelo.
>
>
>
> "Hastie,
>
> Christa" To: [EMAIL PROTECTED]
>
> <chastie@sonypic cc:
>
> tures.com> Subject: ::massive sql query
> using dbi - please help::
>
>
> 02/11/2001 15:57
>
> Please respond
>
> to "Hastie,
>
> Christa"
>
>
>
>
>
> Hello to all!
> This is my first time posting to this group! But I'm in desperate need of
> any help!
> (BTW, thanks for all the emails from contributors to this list..I learn a
> lot from you guys every day!)
>
> I have two tables in a mySQL db, named users_old and users_new, both with
> UserId and Email columns, no primary keys and no auto-increment columns.
> The users_old table has numeric values for the UserId while the users_new
> have NULL values.
> The users_old table has 120,000 rows and the users_new has 910,000 rows.
> I'm trying to find a simple, painless way of querying these two tables so I
> can store the UserId and Email from the users_old table if the Email exists
> in both tables.
>
> Everything I try just continues to run without ever producing any results -
> it just hangs at the command line when running the standalone query.
> Perhaps there are just too many rows to compare.
> I tried writing a simple script using the Perl DBI to just log the results
> of this massive query in a simple tab delimited flat file so I can load the
> data into the live database after it finishes....but no luck.
>
> Anybody have any suggestions on a better approach?
> My simple code looks like this:
>
> #!/usr/local/bin/perl
>
> use DBI;
> use strict;
>
> my($dbh);
> my($sth);
> my($exclusive_lock);
>
> eval { $dbh = DBI->connect("DBI:mysql:dbname;host=localhost", "dbuser",
> "dbpassword", {'RaiseError' => 1}); };
> if($@) {
> my($error) = "Error opening Database: $@\n";
> print "$error\n";
> }
>
> my $sth = $dbh->prepare("SELECT users_old.UserId, users_old.Email FROM
> users_new, users_old WHERE users_old.Email = users_new.Email");
> $sth->execute or die "Unable to execute query: $dbh->errstr\n";
> my ($row);
>
> while($row = $sth->fetchrow_arrayref) {
> my($data_log) = "/home/chastie/sony_showbiz.txt";
> open (DATATEMP, ">>$data_log");
> flock (DATATEMP, $exclusive_lock);
> print LOG "$row->[0]\t$row->[1]\n";
> close (LOG);
> }
>
> $sth->finish;
> $dbh->disconnect;
> exit;
>
> \||||/
> (o o)
> ooO-(_)-Ooo--------------------------------
> christa hastie
> programmer
> sonypicturesdigitalentertainment
> www.sonypictures.com
> -------------------------------------------------
