Thanks for a good hint & code.

I'm using DBD:DB2.
Maybe quatation (') rwapping data must be dropped, and
"CURRENT TIMESTAMP" keyword will be treated.

my @new_values = ();
my @place_holders =
  map { 
    if (/^CURRENT TIMESTAMP$/) {
      $_;
    } else {
      push @new_values => $_;
      "?";
    }
  } @args
;

I've read DBI doc but I cann't understand prepare_cached.
Any difference between prepare and prepare_cached?

  my $q = join ',' => map { '?' } @args;
  my $sql = "$name VALUES ($q)";
  if (!exists $sth{$sql}) {
    $sth{$sql} = $dbh->prepare_cached($sql);
  }
  return $sth{$name}->execute(@args);


Regards,
Hirosi Taguti
[EMAIL PROTECTED]


> -----Original Message-----
> From: Greg Sabino Mullane [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 22, 2005 10:26 AM
> To: [EMAIL PROTECTED]
> Cc: Hirosi Taguti
> Subject: Re: Memoizing non-place-holding sql statements to 
> place-holding
> 
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> 
> > Is there a module already existing to memoize non-place-holding sql
> > statements to place-holding ones and suppress prepare to minimum?
> 
> If I am understanding you correctly, there is no such module, although
> there are some modules out there that attempt to deconstruct SQL
> commands that might be adapted to the task. Far better to clean up
> the application however, and force it to use a normal prepare/execute
> scheme with placeholders.
>   
> > I'm now transforming other peaple's scripts for a 
> performance inprovement.
> > This system is so big, I cann't understand quickly, but I 
> foud that all
> > SQL statements are made wothout place holders, and all 
> INSERT statements
> > are processed by the next subrourine.
>   
> > sub insert_sql {
> >         my ($dbh, $sql) = @_;
> >         my $sth = $dbh->prepare($sql) or die;
> >         $sth->execute();
> >         $sth->finish;
> >
> 
> If you use prepare_cached() instead of prepare(), you may 
> gain some advantage.
> If you are not using placeholders, you are probably better 
> off using do()
> instead of the prepare/execute method. (also, the sth->finish 
> above is not
> necessary for inserts).
> 
> > I think this idea can be used to memoize each INSERT 
> statement as one with
> > place holders, prepare only once for each SQL and execute 
> "execute" method
> > for each call.
>   
> That's not a bad hack if the code is really too bad to go 
> back and change
> for the better. It really depends on which RDBMS you are using and how
> complex the SQL is, of course, but here's a first rough stab I whipped
> out which I think does what you want:
> 
> sub insert_sql {
>   my ($dbh,$sql) = @_;
>   if ($sql !~ 
> /^\s*(INSERT\s+INTO\s+[^\(]+\s*\(\s*[^\)]+\s*\))\s+VALUES\s+\(
> (.+)\)\s*$/io) {
>     my $line = (caller)[2];
>     warn "insert_sql was unable to parse the statement from 
> line $line: $sql\n";
>     return $dbh->do($sql);
>   }
>   my ($name,$args) = ($1,$2);
>   my @args = split /,/ => $args;
>   if (!exists $sth{$name}) {
>     my $q = join ',' => map { '?' } @args;
>     $sth{$name} = $dbh->prepare_cached("$name VALUES ($q)");
>   }
>   return $sth{$name}->execute(@args);
> }
> 



Reply via email to