Re^2: Multiple queries on DBI corresponding to multiple csv files?

Appreciate the tips!

I have started to look in the DBI error handle, really cool. I.e.:

my %attr =
(
    AutoCommit => 0, # Require explicit commit or rollback.
    PrintError => 1, 
    ShowErrorStatement => 0,
    RaiseError => 0,
    HandleError => \&Usage,
);
[download]

On my TODO list:

replace "," for "|"

replace Text::CSV_XS for Perl CSV module

Cheers!

Comment on Re^2: Multiple queries on DBI corresponding to multiple csv files? Download Code

Replies are listed 'Best First'.

Re^3: Multiple queries on DBI corresponding to multiple csv files?
by Marshall (Canon) on Feb 22, 2019 at 00:45 UTC

Normally, you don't have to fiddle with "AutoCommit" as the default is "true", at least it is with SQLite. Basically you want write operations to commit automatically unless you override that behavior explicitly.

A commit operation is very expensive - the DB has to do a lot of work to make sure the data is "really there". The DB will have a limit on the number of transactions per second (basically commits). The number of operations per second will be more than an order of magnitude more than that. One reason to manually control the start/conclusion of a transaction is when doing a lot of inserts at once. For example:

my $addRoster = $dbh->prepare ("INSERT INTO Roster (Call,Owner)
                                VALUES (?,?)"); 
                                
$dbh->do("BEGIN");  # start a new transaction for all inserts

foreach my $row (@$info)  #the 'roster' sheet of Roster.xls  #20 Feb 2
+018
{
    ...blah...blah...
    
    $addRoster -> execute ($callsign, $owner);  
} 

$dbh->do("COMMIT"); #massive speed up by making one transaction!!
[download]

[reply]
[d/l]


Don't ask to ask, just ask
	PerlMonks