Index: main/trunk/greenstone2/perllib/gssql.pm
===================================================================
--- main/trunk/greenstone2/perllib/gssql.pm	(revision 32573)
+++ main/trunk/greenstone2/perllib/gssql.pm	(revision 32574)
@@ -356,4 +356,9 @@
 # "Many methods have an optional \%attr parameter which can be used to pass information to the driver implementing the method. Except where specifically documented, the \%attr parameter can only be used to pass driver specific hints. In general, you can ignore \%attr parameters or pass it as undef."
 
+# More efficient to use prepare() to prepare an SQL statement once and then execute() it many times
+# (binding different values to placeholders) than running do() which will prepare each time and
+# execute each time. Also, do() is not useful with SQL select statements as it doesn't fetch rows.
+# Can prepare and cache prepared statements or retrieve prepared statements if cached in one step:
+# https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#prepare_cached
 
 # https://www.guru99.com/insert-into.html
@@ -374,21 +379,8 @@
     my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only, $debug_out) = @_;
     
-    #my ($did, $sid, $metaname, $metavalue) = @_;
-    my $dbh = $self->{'db_handle'};
-
-    my $sth = $self->{"sth_insert_into_meta_table"};
-    if(!$sth) {
-	my $tablename = $self->get_metadata_table_name();
-	
-	#my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ?
-	
-	# using qq{} since we want $tablename placeholder to be filled in
-	# returns Statement Handle object!
-	$sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n");
-
-	print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
-	
-	$self->{"sth_insert_into_meta_table"} = $sth; # store it for future use
-    }
+    my $dbh = $self->{'db_handle'};
+    
+    my $tablename = $self->get_metadata_table_name();
+    my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n");
 
     # Now we're ready to execute the command, unless we're only debugging
@@ -414,19 +406,7 @@
     my $dbh = $self->{'db_handle'};
     
-    my $sth = $self->{"sth_insert_into_fulltxt_table"};
-    if(!$sth) {
-	my $tablename = $self->get_fulltext_table_name();
-
-	#my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ?
-	
-	# using qq{} since we want $tablename placeholder to be filled in
-	# returns Statement Handle object!
-	$sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n");
-	
-	print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
-
-	$self->{"sth_insert_into_fulltxt_table"} = $sth; # store it for future use
-    }
-
+    my $tablename = $self->get_fulltext_table_name();
+    my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n");
+    
     # Now we're ready to execute the command, unless we're only debugging
 
@@ -444,4 +424,5 @@
 ## The 2 select statements used by GreenstoneSQLPlugin
 
+#https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref
 # Returns the statement handle that prepared and executed
 # a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
@@ -457,5 +438,5 @@
     my $meta_table = $self->get_metadata_table_name();
     
-    my $sth = $dbh->prepare(qq{SELECT * FROM $meta_table WHERE did = ?});
+    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $meta_table WHERE did = ?});
     $sth->execute( $oid ); # will print msg on fail
     
@@ -463,4 +444,6 @@
 }
 
+
+#https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref
 # Returns the statement handle that prepared and executed
 # a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
@@ -473,5 +456,5 @@
     my $fulltxt_table = $self->get_fulltext_table_name();
     
-    my $sth = $dbh->prepare(qq{SELECT * FROM $fulltxt_table WHERE did = ?});
+    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $fulltxt_table WHERE did = ?});
     $sth->execute( $oid ); # will print msg on fail
     
@@ -489,16 +472,6 @@
     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;
-
-    # 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;
-    }
+    my $tablename = $self->get_metadata_table_name();
+    my $sth = $dbh->prepare_cached(qq{DELETE FROM $tablename WHERE did = ?});
     $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn()
 }
@@ -510,16 +483,7 @@
     
     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;
-    }
+
+    my $tablename = $self->get_fulltext_table_name();
+    my $sth = $dbh->prepare_cached(qq{DELETE FROM $tablename WHERE did = ?});
     $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn()
 }
