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);
}

- --
Greg Sabino Mullane greg@xxxxxxxxxxxx
PGP Key: 0x14964AC8 200508212105
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDCSlevJuQZxSWSsgRAsouAJ4/UKGIe/8SZSoLT/Z0fUSr0t6aQwCgvBpp
9B8kW2E/pzSi+ykFFKCz7UQ=
=7Xaq
-----END PGP SIGNATURE-----


.

Relevant Pages

  • Memoizing non-place-holding sql statements to place-holding
    ... Is there a module already existing to memoize non-place-holding sql ... SQL statements are made wothout place holders, ... For a small input data, about 4,000 INSERT are processed, ... prepare only once for each SQL and execute "execute" method ...
    (perl.dbi.users)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • Re: SQL Injection- Bypassing magic_quotes
    ... Because i was trying to execute: ... Don't terminate the query and you most ... Or try something like bobcat or one of the other SQL injection tools out ... Chief Information Security Officer ...
    (Pen-Test)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... explicitly code dummy words as bind variable placekeepers, put the host variables on the ...
    (comp.lang.cobol)
  • Re: Poor performance when executing stored procedure
    ... > Generally I would write stored procedures to do only one job. ... If SQL Server ... > which is even worse those sps can execute each other. ... > then executed spOrders which is executed in spCustomers and then got stuck ...
    (microsoft.public.sqlserver.programming)