Your question is not clear, and there are a lot of mistakes in your code snippet, so I'm not sure if I understand what you want, but I think it's something like this:
You have a database with a couple tables, and you have a file containing lines of text arranged in columns. You want to run a query on the database tables with an SQL statement whose "where ..." clause uses the first column value from each line of the file. Is that what you mean?
Running the query once for every line of input from the file might actually be the right idea, if you prepare the SQL statement only one time, and then execute it on each row of input, like this:
use DBI;
my $dbh = DBI->connect( ... ); # get your database handle
my $sql = "select i.name, i._eid, i.num, i.val, m.code from ...".
" where i.name = m.code and m.code = ?"; # "?" is a "placeh
+older"
my $sth = $dbh->prepare( $sql );
open( R, "<", $datafile_name ) or die "$datafile_name: $!\n";
while (<R>)
{
chomp;
my ( $ref ) = ( /^([^\t]+)/ );
$sth->execute( $ref ); # value passed to execute() fills the plac
+eholder
# use one of the DBI methods for getting the query results...
}
There might be other ways to get what you want, but this is the simplest way.
Another approach would be to create a temporary table with just one field, load it with the values from your data file, and do a single query execution using an sql statement like this, and then drop the temporary table:
"select ... from ... where i.name = m.code and m.code in (select ref f
+rom tmp)"
One last possibility would be to store your data file values into a hash, run a single query that returns all the available database rows, and filter the rows by checking whether the "m.code" value exists in the hash -- something like:
my %ref;
open( R, "<", $datafile_name );
while (<R>) {
if ( /^([^\t]+)/ ) {
$ref{$1} = undef;
}
}
my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare( "select m.code, ... from ... where i.name=m.c
+ode");
$sth->execute;
my $rows = $sth->fetchall_arrayref;
for my $r ( @$rows ) {
my ( $mcode, ... ) = @$r;
next unless exists( $ref{$mcode} );
# do something ...
}
As for performance, that depends on things you haven't told us: How many lines are in the data file? How big are the tables, and if they're really big, are they set up to use efficient indexes on the fields that are involved in the query?
You should probably try the simplest thing first, and if that isn't good enough, either improve the database indexing, or another approach.
|