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
Happy to see that you are investigating some of these other options! 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] The above code slams data from an Excel spreadsheet into a DB. The sequence is typical. An SQL statement is prepared outside the loop and then executed many times with different data values. An SQL prepare statement is table specific, so this can't be done with a table name as a variable. The above code has been in production for a year. It takes <1 second to run. If I take out the BEGIN and COMMIT statements, it takes ~10 seconds -> 10x difference. Without the BEGIN and COMMIT statements, each insert would be committed automatically. You can run 1 million inserts as a single transaction and I have code that does that. This can make the difference between say 20 seconds and 4-6 hours!	[reply] [d/l]

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]