Changeset 32571


Ignore:
Timestamp:
11/05/18 16:46:34 (2 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:
2 edited

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'};
Note: See TracChangeset for help on using the changeset viewer.