Changeset 32571

Show
Ignore:
Timestamp:
05.11.2018 16:46:34 (2 weeks 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:
2 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/gssql.pm

    r32563 r32571  
    229229} 
    230230 
     231 
    231232# We should already have done "use <database>;" if this gets called. 
    232233# Just load this collection's metatable 
     
    454455     
    455456    my $dbh = $self->{'db_handle'}; 
    456     my $meta_table = $self->get_metadata_table_name(); 
    457      
    458     #my $rows_deleted =  
    459     $dbh->do(qq{DELETE FROM $meta_table WHERE did = ?}, undef, $oid) 
    460     or warn $dbh->errstr; 
     457 
     458    #my $meta_table = $self->get_metadata_table_name(); 
     459    ##my $rows_deleted =  
     460    #$dbh->do(qq{DELETE FROM $meta_table WHERE did = ?}, undef, $oid) or warn $dbh->errstr; 
     461 
     462    # If we do many deletes from this table,  
     463    # it may be more optimal to maintain a delete statement to repeatedly execute 
     464    my $sth = $self->{"sth_del_from_meta_table"}; 
     465    if(!$sth) { 
     466    my $meta_table = $self->get_metadata_table_name(); 
     467    $sth = $dbh->prepare(qq{DELETE FROM $meta_table WHERE did = ?}); 
     468    $self->{"sth_del_from_meta_table"} = $sth; 
     469    } 
     470    $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn() 
    461471} 
    462472 
     
    466476    my ($oid) = @_; 
    467477     
    468     my $dbh = $self->{'db_handle'};     
    469     my $fulltxt_table = $self->get_fulltext_table_name(); 
    470      
    471     $dbh->do(qq{DELETE FROM $fulltxt_table WHERE did = ?}, undef, $oid) 
    472     or warn $dbh->errstr; 
     478    my $dbh = $self->{'db_handle'}; 
     479     
     480    #my $fulltxt_table = $self->get_fulltext_table_name(); 
     481    #$dbh->do(qq{DELETE FROM $fulltxt_table WHERE did = ?}, undef, $oid) or warn $dbh->errstr;     
     482 
     483    # If we do many deletes from this table,  
     484    # it may be more optimal to maintain a delete statement to repeatedly execute 
     485    my $sth = $self->{"sth_del_from_fulltxt_table"}; 
     486    if(!$sth) { 
     487    my $fulltxt_table = $self->get_fulltext_table_name(); 
     488    $sth = $dbh->prepare(qq{DELETE FROM $fulltxt_table WHERE did = ?}); 
     489    $self->{"sth_del_from_fulltxt_table"} = $sth; 
     490    } 
     491    $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn() 
    473492} 
    474493 
  • main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm

    r32570 r32571  
    230230     
    231231    my $gs_sql = $self->{'gs_sql'} || return 0; # couldn't make the connection or no db etc 
    232     if(scalar (@$oids) > 1) { 
    233     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"; 
    234     } 
    235232     
    236233    my $proc_mode = $self->{'process_mode'};