> -----Original Message-----
> From: Lothar Behrens [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 31, 2001 9:33 AM
> To: Perl Beginners (E-Mail)
> Subject: perl and DBI
>
>
> Hello,
>
> I am new to perl and work on sample scripts as base for my problem.
> But like to solve a problem concerning database issues.
>
> Please hint me to an advanced mailing list, if this list is the wrong
> place.
>
> I don't know, how the memory management works. But my script consumes
> about 500 MB !
> This is because I have to fill ca. 400 K lines of data into a
> database.
> The script dies after 27 K of that lines.
>
> Tests with some data works. When the data comes from a extra
> file, can I
> then handle this amount of data ?
>
> Thanks
>
> Lothar Behrens
>
> The part of code that inserts the data is like this:
>
> while (<DATA>)
> {
> chomp;
> my(@field) = split(/\;/, $_);
> # Note the single quotes in the varchar fields.
> $sql = "if not exists (select KUNDENNUMMER from
> $tableName where
> KUNDENNUMMER = '$field[0]') insert into \
> $tableName (KUNDENNUMMER) values ('$field[0]')";
> # $sth = $dbh -> prepare($sql) || die("Can't prepare
> statement:
> $DBI::errstr");
> # $rc = $sth->execute() || die("Can't execute statement:
> $DBI::errstr");
> $rc = $dbh->do($sql) || die("Can't execute statement:
> $DBI::errstr");
> $rc = $dbh->commit() || die("Can't commit data:
> $DBI::errstr");
> }
>
> ...
>
> __END__
> 111111
> 222222
> 333333
> 444444
> ......
I don't know why you are running out of memory, but some comments:
1. You are using both prepare()/execute() and do(). You should only
use one or the other. In effect, you are executing the query twice for
each input value.
2. If your database supports placeholders, use those and move the
prepare() outside the loop. This will typically be much faster,
especially with a large number of rows. I don't know what database
you're using, but with Oracle you can do something like this:
my $sth = $dbh->prepare(q[insert into foo values (?, ?)]);
while( ... ) {
$sth->execute('bar', 'baz');
}
3. Committing every row will generally be very slow. You may want
to commit batches of rows.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
