I was confused by the role of the bind variables for Postgres
applications so I created 2 scripts, doing the same thing, with or
without bind variables. The difference is staggering. Here are the
scripts, first with binds:

mgogala@ubuntu:/tmp$ cat test_bind
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=scott","mgogala","", {AutoCommit =>
0});
my $INS = "insert into TAB1 values(?,?)";
my $sth = $dbh->prepare($INS);
foreach my $i (1 .. 100000)
{
$sth->execute(($i,"Postgres"));
}
$dbh->commit();

END
{
$dbh->disconnect if defined($dbh);
}

And without binds:

mgogala@ubuntu:/tmp$ cat test_nobind
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=scott","mgogala","", {AutoCommit =>
0});
my $INS = "insert into TAB1 values(";
foreach my $i (1 .. 100000)
{
my $SQL=$INS.$i.",'Postgres')";
my $sth=$dbh->prepare($SQL);
$sth->execute();
}
$dbh->commit();

END
{
$dbh->disconnect if defined($dbh);
}


The difference in speed is enormous:

mgogala@ubuntu:/tmp$ time ./test_nobind

real 0m59.129s
user 0m17.933s
sys 0m8.965s
mgogala@ubuntu:/tmp$ time ./test_bind

real 0m20.358s
user 0m5.768s
sys 0m2.640s
mgogala@ubuntu:/tmp$


Inserts with bind variables are 2.5 times faster than without bind
variables. Anybody care to comment?
--
http://mgogala.byethost5.com

On Sun, 08 Aug 2010 23:13:55 +0000, Mladen Gogala wrote:

The table looks like this:
scott=# \d tab1
Table "public.tab1"
Column | Type | Modifiers
--------+-----------------------+-----------
col1 | bigint | not null
col2 | character varying(16) |
Indexes:
"tab1_pkey" PRIMARY KEY, btree (col1)


Postgres version is 8.4.4 on my laptop (Compaq nc6400).

--
http://mgogala.byethost5.com

Mladen Gogala wrote:

Compiling (preparing) the sql statement takes time and resources. The
same happens with Oracle too just as it happens with any backend that
allows one to prepare a statement once with bind variables to execute
and re-execute.

On Sun, 08 Aug 2010 21:19:26 -0300, Bob Badour wrote:

Actually, the motivation for this test came from the 1st chapter of the
Tom Kyte's new book. Unfortunately, from what I have seen around me,
people think that Postgres is different and the applications are
frequently using the trick from the "nobind" script, with assembling SQL
from the input and then executing it. In addition to being insecure
because of the SQL injection, such practice also produces underperforming
apps.



--
http://mgogala.byethost5.com

Mladen Gogala wrote:

I don't think anyone would disagree.

Am 09.08.2010 01:13, schrieb Mladen Gogala:
For a complete test I would like to see the same prog with no
bind variables and no prepare step, just directly execute the sql
string.

Max

On Wed, 11 Aug 2010 14:05:33 +0200, M. Strobel wrote:

Hmmm, results are strange:

mgogala@nycwxp2622:~$ time /tmp/test_nobind2

real 0m17.880s
user 0m3.244s
sys 0m1.100s
mgogala@nycwxp2622:~$

The "test_nobind2" script is written according to your specification:
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=scott","mgogala","", {AutoCommit =>
0});
$dbh->{pg_server_prepare} = 0;
my $INS = "insert into TAB1 values(";
foreach my $i (1 .. 100000)
{
my $SQL=$INS.$i.",'Postgres')";
$dbh->do($SQL);

}
$dbh->commit();

END
{
$dbh->disconnect if defined($dbh);
}

That means that "prepare" is the most expensive part of the execution. If
I allow it to use already prepared statements, it's faster still:

mgogala@nycwxp2622:~$ time /tmp/test_nobind2

real 0m17.803s
user 0m3.124s
sys 0m1.088s

That, of course, has pg_server_prepare set to "1".


--
http://mgogala.byethost5.com