Changeset 32571

Timestamp:

2018-11-05T16:46:34+13:00 (5 years ago)

Author:

ak19

Message:

Optimised the SQL DB delete operations in case there are several in one building phase, by preparing the delete SQL statement once and storing it then executing it each time there's a delete, rather than calling do() which will both prepare the del SQL statement each time and execute it each time

Location:

main/trunk/greenstone2/perllib

Files:

• gssql.pm (modified) (3 diffs)
• plugins/GreenstoneSQLPlugin.pm (modified) (1 diff)

main/trunk/greenstone2/perllib/gssql.pm

@@ -229 +229 @@
 }
 
+
 # We should already have done "use <database>;" if this gets called.
 # Just load this collection's metatable
@@ -454 +455 @@
     
     my $dbh = $self->{'db_handle'};
-    my $meta_table = $self->get_metadata_table_name();
-    
-    #my $rows_deleted = 
-    $dbh->do(qq{DELETE FROM $meta_table WHERE did = ?}, undef, $oid)
-    or warn $dbh->errstr;
+
+    #my $meta_table = $self->get_metadata_table_name();
+    ##my $rows_deleted = 
+    #$dbh->do(qq{DELETE FROM $meta_table WHERE did = ?}, undef, $oid) or warn $dbh->errstr;
+
+    # If we do many deletes from this table, 
+    # it may be more optimal to maintain a delete statement to repeatedly execute
+    my $sth = $self->{"sth_del_from_meta_table"};
+    if(!$sth) {
+    my $meta_table = $self->get_metadata_table_name();
+    $sth = $dbh->prepare(qq{DELETE FROM $meta_table WHERE did = ?});
+    $self->{"sth_del_from_meta_table"} = $sth;
+    }
+    $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn()
 }
 
@@ -466 +476 @@
     my ($oid) = @_;
     
-    my $dbh = $self->{'db_handle'};    
-    my $fulltxt_table = $self->get_fulltext_table_name();
-    
-    $dbh->do(qq{DELETE FROM $fulltxt_table WHERE did = ?}, undef, $oid)
-    or warn $dbh->errstr;
+    my $dbh = $self->{'db_handle'};
+    
+    #my $fulltxt_table = $self->get_fulltext_table_name();
+    #$dbh->do(qq{DELETE FROM $fulltxt_table WHERE did = ?}, undef, $oid) or warn $dbh->errstr;    
+
+    # If we do many deletes from this table, 
+    # it may be more optimal to maintain a delete statement to repeatedly execute
+    my $sth = $self->{"sth_del_from_fulltxt_table"};
+    if(!$sth) {
+    my $fulltxt_table = $self->get_fulltext_table_name();
+    $sth = $dbh->prepare(qq{DELETE FROM $fulltxt_table WHERE did = ?});
+    $self->{"sth_del_from_fulltxt_table"} = $sth;
+    }
+    $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn()
 }
 

main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm

@@ -230 +230 @@
     
     my $gs_sql = $self->{'gs_sql'} || return 0; # couldn't make the connection or no db etc
-    if(scalar (@$oids) > 1) {
-    print STDERR "TODO: As there's more than 1 docid, we now have reason to optimise GreenstoneSQLPlugin::remove_one() by using dbi::prepare() + dbi::execute() instead of dbi::do().\n";
-    }
     
     my $proc_mode = $self->{'process_mode'};