Memoizing non-place-holding sql statements to place-holding

Hello,

Is there a module already existing to memoize non-place-holding sql
statements to place-holding ones and suppress prepare to minimum?

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

INSERT'ed tables are almost 10 tables and almost 20 kind of INSERT statements
are processed. For a small input data, about 4,000 INSERT are processed,
for big input data, about 40,000 INSERT are processed,

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.

This coding may not be so difficult, but not easy for me.

I don't think "Memoize.pm" can be used, :)

Any idea?

Regards,
Hirosi Taguti
h-taguchi@secom.co.jp

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@turnstep.com
PGP Key: 0x14964AC8 200508212105
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

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

RE: Memoizing non-place-holding sql statements to place-holding

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
h-taguchi@secom.co.jp


> -----Original Message-----
> From: Greg Sabino Mullane [mailto:greg@turnstep.com]
> Sent: Monday, August 22, 2005 10:26 AM
> To: dbi-users@perl.org
> 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);
> }
>

RE: Memoizing non-place-holding sql statements to place-holding

Sorry, I mean...

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

Regards,
Hirosi Taguti
h-taguchi@secom.co.jp

Re: Memoizing non-place-holding sql statements to place-holding

Greg Sabino Mullane wrote:

>there are some modules out there that attempt to deconstruct SQL
>commands that might be adapted to the task
>
As, for example SQL::Statement which would catch a few things your hack
wouldn't - e.g. embedded escaped quotes in values, or INSERT statements
that don't specify the column names (which are valid even if unwise).

#!/usr/bin/perl -w
use strict;
use SQL::Statement;
my $sql = q{ INSERT INTO MyTbl (col1,col2) VALUES (7,'o''brian') };
my ($pholder_sql,@vals) = make_pholders( $sql );
print "$pholder_sql [@vals]\n";

# output : INSERT INTO MYTBL (COL1,COL2) VALUES (?,?) [7 o'brian]

sub make_pholders {
my($sql) = @_;
my $stmt = SQL::Statement->new( $sql );
my($table) = map {$_->name} $stmt->tables;
my($columns) = join ',',map {$_->name} $stmt->columns;
my(@values) = $stmt->row_values;
my $pholder = join ',',('?')x@values;
my $pholder_sql = "INSERT INTO $table ($columns) VALUES ($pholder)";
return $pholder_sql,@values;
}
__END__

--
Jeff

Re: Memoizing non-place-holding sql statements to place-holding

On 8/21/05 10:38 PM, h-taguchi@secom.co.jp wrote:
> 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'm using Informix, which is apparently (and perhaps not surprisingly)
similar to DB2 in this regard. I had to do something similar to what you
describe above in my RDBMS-OO mapper module:

http://search.cpan.org/~jsiracusa/Rose-DB-Object-0.0692/lib/ Rose/DB/Object/M
etadata.pm#allow_inline_column_values

It's a bit of a pain, but until DBD::Informix becomes "smart" enough to
detect this on its own (if that's even possible) it's something that has to
be handled by the programmer (or, in my case, by the programmer's library :)

-John

RE: Memoizing non-place-holding sql statements to place-holding

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

....

> It's a bit of a pain, but until DBD::Informix becomes "smart"
> enough to
> detect this on its own (if that's even possible) it's

SQL::Statement can do this?
I'm lea(r)ning (to) it today, but User-Defined Functions are used differently.

Hirosi Taguti
h-taguchi@secom.co.jp

Re: Memoizing non-place-holding sql statements to place-holding

h-taguchi@secom.co.jp wrote:

>>> my @new_values = ();
>>> my @place_holders =
>>> map {
>>> if (/^CURRENT TIMESTAMP$/) {
>>> $_;
>>> } else {
>>> push @new_values => $_;
>>> "?";
>>> }
>>> } @args
>>> ;
>>
>
>SQL::Statement can do this?
>
Yes. The snippet I sent before will automatically turn #1 into #2.

#1 INSERT INTO MyTbl (col1,CURRENT_TIMESTAMP,col2)
VALUES (7,'o''brian') };

#2 INSERT INTO MYTBL (COL1,CURRENT_TIMESTAMP,COL2)
VALUES (?,?)

And return (7,q{o'brian}) as @vals.

--
Jeff

RE: Memoizing non-place-holding sql statements to place-holding

> >>> my @new_values = ();
> >>> my @place_holders =
> >>> map {
> >>> if (/^CURRENT TIMESTAMP$/) {
> >>> $_;
> >>> } else {
> >>> push @new_values => $_;
> >>> "?";
> >>> }
> >>> } @args
> >>> ;
> >>
> >
> >SQL::Statement can do this?
> >
> Yes. The snippet I sent before will automatically turn #1 into #2.
>
> #1 INSERT INTO MyTbl (col1,CURRENT_TIMESTAMP,col2)
> VALUES (7,'o''brian') };
>
> #2 INSERT INTO MYTBL (COL1,CURRENT_TIMESTAMP,COL2)
> VALUES (?,?)
>
> And return (7,q{o'brian}) as @vals.

I'm dealing with:

INSERT INTO MyTbl (col1,record_ts,col2)
VALUES (7,CURRENT TIMESTAMP,'o''brian') };

SQL ERROR: 'CURRENT TIMESTAMP' is not a valid value or is not quoted!

"CURRENT TIMESTAMP" is a keyword used by DB2.

Regards,
Hirosi Taguti
h-taguchi@secom.co.jp

Re: Memoizing non-place-holding sql statements to place-holding

h-taguchi@secom.co.jp wrote:

>I'm dealing with:
>
>INSERT INTO MyTbl (col1,record_ts,col2)
> VALUES (7,CURRENT TIMESTAMP,'o''brian') };
>
That calls for a combination of SQL::Statement and your method. You have
to trick SQL::Statement into thinking that "CURRENT TIMESTAMP" is a
string (because it doesn't currently handle functions with spaces in
their names. So the code below takes your input and returns:

INSERT INTO MYTBL
(COL1,RECORD_TS,COL2)
VALUES (?,CURRENT TIMESTAMP,?)

With [7 o'brian] as the values.

sub make_pholders {
my($sql) = @_;
$sql =~ s/(CURRENT TIMESTAMP)/'$1'/g;
my $stmt = SQL::Statement->new( $sql );
my($table) = map {$_->name} $stmt->tables;
my($columns) = join ',',map {$_->name} $stmt->columns;
my @values;
my @pholders = map {
if (/^CURRENT TIMESTAMP$/) {
$_
}
else {
push @values => $_;
'?';
}
} $stmt->row_values;
my $pholder = join ',',@pholders;
my $pholder_sql =
"INSERT INTO $table ($columns) VALUES ($pholder)"
;
return $pholder_sql,@values;
}

I hope this helps.

--
Jeff

RE: Memoizing non-place-holding sql statements to place-holding

Thank you so much.
I was checking UDFs.

Now SQL::Statement has become my tool.
Thank you again.

Regards,
Hirosi Taguti
h-taguchi@secom.co.jp

> >INSERT INTO MyTbl (col1,record_ts,col2)
> > VALUES (7,CURRENT TIMESTAMP,'o''brian') };
> >
> That calls for a combination of SQL::Statement and your
> method. You have
> to trick SQL::Statement into thinking that "CURRENT TIMESTAMP" is a
> string (because it doesn't currently handle functions with spaces in
> their names. So the code below takes your input and returns:
>
> INSERT INTO MYTBL
> (COL1,RECORD_TS,COL2)
> VALUES (?,CURRENT TIMESTAMP,?)
>
> With [7 o'brian] as the values.
>
> sub make_pholders {
> my($sql) = @_;
> $sql =~ s/(CURRENT TIMESTAMP)/'$1'/g;
> my $stmt = SQL::Statement->new( $sql );
> my($table) = map {$_->name} $stmt->tables;
> my($columns) = join ',',map {$_->name} $stmt->columns;
> my @values;
> my @pholders = map {
> if (/^CURRENT TIMESTAMP$/) {
> $_
> }
> else {
> push @values => $_;
> '?';
> }
> } $stmt->row_values;
> my $pholder = join ',',@pholders;
> my $pholder_sql =
> "INSERT INTO $table ($columns) VALUES ($pholder)"
> ;
> return $pholder_sql,@values;
> }
>
> I hope this helps.
>

Re: Memoizing non-place-holding sql statements to place-holding

------=_Part_274_22158424.1124772159517
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 8/22/05, John Siracusa wrote:
>=20
> On 8/21/05 10:38 PM, h-taguchi@secom.co.jp wrote:
> > I'm using DBD:DB2.
> > Maybe quatation (') rwapping data must be dropped, and
> > "CURRENT TIMESTAMP" keyword will be treated.
> >
> > my @new_values =3D ();
> > my @place_holders =3D
> > map {
> > if (/^CURRENT TIMESTAMP$/) {
> > $_;
> > } else {
> > push @new_values =3D> $_;
> > "?";
> > }
> > } @args
> > ;
>=20
> I'm using Informix, which is apparently (and perhaps not surprisingly)
> similar to DB2 in this regard. I had to do something similar to what you
> describe above in my RDBMS-OO mapper module:
>=20
>=20
> http://search.cpan.org/~jsiracusa/Rose-DB-Object-0.0692/lib/ Rose/DB/Objec=
t/M
> etadata.pm#allow_inline_column_values
>=20
> It's a bit of a pain, but until DBD::Informix becomes "smart" enough to
> detect this on its own (if that's even possible) it's something that has=
=20
> to
> be handled by the programmer (or, in my case, by the programmer's library=
=20
> :)



Patches are welcome - with test suite, etc.

The DBD::Informix code already has a function sqltoken() - in sqltoken.c an=
d=20
sqltoken.h - which parses SQL and counts placeholders; see also=20
dbd_ix_preparse() in dbdimp.ec (which is an alternative=
=20
ad hoc solution based in part on the preparse() from either DBI or=20
DBD::Oracle of about 8 years ago).

You'd have to do a little work - it doesn't identify what type of token it=
=20
has found. There's also a 'cover function' called iustoken() available (wit=
h=20
SQLCMD, which can be downloaded from the Software Archive at the IIUG=20
http://www.iiug.org/software/, or from my private web site - contact me if=
=20
you need the code and can't get hold of it). This deals with a particular=
=20
nasty related to SET, MULTISET and LIST values in IUS (9.x or 10.x) Informi=
x=20
database servers.

Using those, you can relatively easily identify the sequence of symbols tha=
t=20
make up the SQL statement. You'd have to worry about compound symbols, such=
=20
as DATETIME(2005-08-22 21:40:22) YEAR TO SECOND, and decide when those can=
=20
be replaced by placeholders in the SQL -- hint, they can't easily be=20
replaced by placeholders in the select-list of a SELECT statement such as=
=20
SELECT DATETIME(2005-08-22 21:40:22)) YEAR TO SECOND AS about_now, CURRENT=
=20
YEAR TO SECOND AS exactly_now FROM 'informix'.systables WHERE tabid =3D 1.

You can then concatenate the non-replacable portions of the SQL with the=20
placeholders for the replacable portions of the SQL, and isolate ther=20
replaced values. The code is deliberately and carefully non-destructive; th=
e=20
tokens are identified by start position and one character after last.

--=20
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_274_22158424.1124772159517--

RE: Memoizing non-place-holding sql statements to place-holding

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

prepare_cached is useful if you are going to re-use
a statement handle. It stores the statement handle
in a hash keyed by the sql statement itself. This
saves re-preparing the statement if it is reused:

For example:

my $sth = $dbh->prepare_cached( 'select foo from bar' );

Calls code like:

my $dbh = shfit;
my $sql = shift;

...

$cached_statments{ $sql } ||= $dbh->prepare( $sql );

This only does the prepare once (when the ||= finds a
false value in %prepared_statements).

These are useful when you are going to re-run the same
query any number of times from different parts of the
code.



--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

RE: Memoizing non-place-holding sql statements to place-holding

Thanks, maybe I understand it.

By using prepare_cached method, I can do without a
hash like %cached_statments, and I can get a benefit from
all modules I'm making.

Regards,
Hirosi Taguti
h-taguchi@secom.co.jp

> -----Original Message-----
> From: Steven Lembark [mailto:lembark@wrkhors.com]
> Sent: Monday, August 29, 2005 2:08 AM
> To: Hirosi Taguti; dbi-users@perl.org
> Subject: RE: Memoizing non-place-holding sql statements to
> place-holding
>
>
> > I've read DBI doc but I cann't understand prepare_cached.
> > Any difference between prepare and prepare_cached?
>
> prepare_cached is useful if you are going to re-use
> a statement handle. It stores the statement handle
> in a hash keyed by the sql statement itself. This
> saves re-preparing the statement if it is reused:
>
> For example:
>
> my $sth = $dbh->prepare_cached( 'select foo from bar' );
>
> Calls code like:
>
> my $dbh = shfit;
> my $sql = shift;
>
> ...
>
> $cached_statments{ $sql } ||= $dbh->prepare( $sql );
>
> This only does the prepare once (when the ||= finds a
> false value in %prepared_statements).
>
> These are useful when you are going to re-run the same
> query any number of times from different parts of the
> code.
>
>
>
> --
> Steven Lembark 85-09 90th Street
> Workhorse Computing Woodhaven, NY 11421
> lembark@wrkhors.com 1 888 359 3508
>

Re: Memoizing non-place-holding sql statements to place-holding

> This coding may not be so difficult, but not easy for me.

The issue will be converting:

insert into foo ( field, fields ) values ( ... );

into

insert into foo ( field, fields ) values ( ?, ?, ? )

After that you can use prepare_cached on the
sql to get a re-usable statement handle and
reduce the preparation.

If the values being inserted are not too
convoluted (e.g., don't involve newlines,
commas, or quotes as data) then a few
splits and regexen should allow you to
standardize the queries and use prepare_cached.

> I don't think "Memoize.pm" can be used, :)

No, because you don't want to short-circut the
acutal call.




--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: Memoizing non-place-holding sql statements to place-holding

------=_Part_3299_3626046.1127723956140
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 9/25/05, Steven Lembark wrote:

> > This coding may not be so difficult, but not easy for me.
>
> The issue will be converting:
>
> insert into foo ( field, fields ) values ( ... );
>
> into
>
> insert into foo ( field, fields ) values ( ?, ?, ? )



....especially if one of the values happens to be a SELECT statement, or a
function call, or something equally fun?

INSERT INTO WhatNot VALUES (1, "abc", (SELECT dismal FROM failure WHERE
antidote =3D 'mercury'), MDY(MONTH(TODAY), 1, YEAR(TODAY) + 2));

This may or may not be standard SQL; it is practical SQL for at least some
DBMS.

--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_3299_3626046.1127723956140--