pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDBD-Pg prepared statement versus plain execution
3+ messages / 3 participants
[nested] [flat]
* DBD-Pg prepared statement versus plain execution
@ 2012-03-21 11:21 Rafael Martinez <r.m.guerrero@usit.uio.no>
0 siblings, 2 replies; 3+ messages in thread
From: Rafael Martinez @ 2012-03-21 11:21 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello
We are having some performance problems with an application that uses
prepared statement heavily.
We have found out that it creates-executes-destroys a prepared statement
*per* statement it sends to the database (pg-9.1) via DBD-Pg.
A normal log entry for a sql-statement looks e.g. like this:
- ----------------------------------------------------------
[2012-03-15 14:49:12.484 CET] LOG: duration: 8.440 ms parse
dbdpg_p32048_3:
SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
(ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
[2012-03-15 14:49:12.485 CET] LOG: duration: 0.087 ms bind
dbdpg_p32048_3:
SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
(ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
[2012-03-15 14:49:12.487 CET] LOG: duration: 1.692 ms execute
dbdpg_p32048_3:
SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
(ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
[2012-03-15 14:49:12.488 CET] LOG: duration: 0.029 ms statement:
DEALLOCATE dbdpg_p32048_3
- ----------------------------------------------------------
As you can see, the parse+bind+deallocate part uses much more time than
the execution part. This is the same for many of the statements send to
the database.
My question is:
Is the parse+bind time reported, a time (not reported) that the planer
will use anyway when running a sql-statement in a normal way or the
parse+bind+deallocate time is *extra* time needed by the prepared statement?
Can we assume that running this application without using prepared
statements will do that it runs faster the time used by
parse+bind+deallocate?
Thanks in advance.
regards,
- --
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk9pubAACgkQBhuKQurGihTYkwCcCFYQRDGWD0yaR+f2FFwKs7gN
RfgAoJdPrAzUhfBfsXmst7/l7LVLisHy
=l7Fl
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DBD-Pg prepared statement versus plain execution
@ 2012-03-21 12:59 ktm@rice.edu <ktm@rice.edu>
parent: Rafael Martinez <r.m.guerrero@usit.uio.no>
1 sibling, 0 replies; 3+ messages in thread
From: ktm@rice.edu @ 2012-03-21 12:59 UTC (permalink / raw)
To: Rafael Martinez <r.m.guerrero@usit.uio.no>; +Cc: pgsql-performance
Hi Rafael,
Try disabling the prepare statement processing in DBD::Pg and
try the timing runs again.
Regards,
Ken
On Wed, Mar 21, 2012 at 12:21:23PM +0100, Rafael Martinez wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello
>
> We are having some performance problems with an application that uses
> prepared statement heavily.
>
> We have found out that it creates-executes-destroys a prepared statement
> *per* statement it sends to the database (pg-9.1) via DBD-Pg.
>
> A normal log entry for a sql-statement looks e.g. like this:
> - ----------------------------------------------------------
> [2012-03-15 14:49:12.484 CET] LOG: duration: 8.440 ms parse
> dbdpg_p32048_3:
>
> SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
> WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
> 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
> ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
> CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
> AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
> (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
>
> [2012-03-15 14:49:12.485 CET] LOG: duration: 0.087 ms bind
> dbdpg_p32048_3:
>
> SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
> WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
> 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
> ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
> CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
> AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
> (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
>
>
> [2012-03-15 14:49:12.487 CET] LOG: duration: 1.692 ms execute
> dbdpg_p32048_3:
>
> SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
> WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
> 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
> ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
> CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
> AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
> (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
>
>
> [2012-03-15 14:49:12.488 CET] LOG: duration: 0.029 ms statement:
> DEALLOCATE dbdpg_p32048_3
> - ----------------------------------------------------------
>
> As you can see, the parse+bind+deallocate part uses much more time than
> the execution part. This is the same for many of the statements send to
> the database.
>
> My question is:
>
> Is the parse+bind time reported, a time (not reported) that the planer
> will use anyway when running a sql-statement in a normal way or the
> parse+bind+deallocate time is *extra* time needed by the prepared statement?
>
> Can we assume that running this application without using prepared
> statements will do that it runs faster the time used by
> parse+bind+deallocate?
>
> Thanks in advance.
>
> regards,
> - --
> Rafael Martinez Guerrero
> Center for Information Technology
> University of Oslo, Norway
>
> PGP Public Key: http://folk.uio.no/rafael/
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk9pubAACgkQBhuKQurGihTYkwCcCFYQRDGWD0yaR+f2FFwKs7gN
> RfgAoJdPrAzUhfBfsXmst7/l7LVLisHy
> =l7Fl
> -----END PGP SIGNATURE-----
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DBD-Pg prepared statement versus plain execution
@ 2012-03-21 13:44 Andy Colson <andy@squeakycode.net>
parent: Rafael Martinez <r.m.guerrero@usit.uio.no>
1 sibling, 0 replies; 3+ messages in thread
From: Andy Colson @ 2012-03-21 13:44 UTC (permalink / raw)
To: Rafael Martinez <r.m.guerrero@usit.uio.no>; +Cc: pgsql-performance
On 3/21/2012 6:21 AM, Rafael Martinez wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello
>
> We are having some performance problems with an application that uses
> prepared statement heavily.
>
> We have found out that it creates-executes-destroys a prepared statement
> *per* statement it sends to the database (pg-9.1) via DBD-Pg.
>
> A normal log entry for a sql-statement looks e.g. like this:
> - ----------------------------------------------------------
> [2012-03-15 14:49:12.484 CET] LOG: duration: 8.440 ms parse
> dbdpg_p32048_3:
>
> SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
> WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
> 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
> ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
> CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
> AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
> (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
>
> [2012-03-15 14:49:12.485 CET] LOG: duration: 0.087 ms bind
> dbdpg_p32048_3:
>
> SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
> WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
> 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
> ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
> CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
> AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
> (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
>
>
> [2012-03-15 14:49:12.487 CET] LOG: duration: 1.692 ms execute
> dbdpg_p32048_3:
>
> SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers
> WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType =
> 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
> ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
> CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0
> AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR
> (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1))
>
>
> [2012-03-15 14:49:12.488 CET] LOG: duration: 0.029 ms statement:
> DEALLOCATE dbdpg_p32048_3
> - ----------------------------------------------------------
>
> As you can see, the parse+bind+deallocate part uses much more time than
> the execution part. This is the same for many of the statements send to
> the database.
>
> My question is:
>
> Is the parse+bind time reported, a time (not reported) that the planer
> will use anyway when running a sql-statement in a normal way or the
> parse+bind+deallocate time is *extra* time needed by the prepared statement?
>
> Can we assume that running this application without using prepared
> statements will do that it runs faster the time used by
> parse+bind+deallocate?
>
> Thanks in advance.
>
> regards,
> - --
> Rafael Martinez Guerrero
> Center for Information Technology
> University of Oslo, Norway
>
> PGP Public Key: http://folk.uio.no/rafael/
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk9pubAACgkQBhuKQurGihTYkwCcCFYQRDGWD0yaR+f2FFwKs7gN
> RfgAoJdPrAzUhfBfsXmst7/l7LVLisHy
> =l7Fl
> -----END PGP SIGNATURE-----
>
What does your perl look like? This would be wrong:
for $key (@list)
{
my $q = $db->prepare('select a from b where c = $1');
$q->execute($key);
$result = $q->fetch;
}
This would be right:
my $q = $db->prepare('select a from b where c = $1');
for $key (@list)
{
$q->execute($key);
$result = $q->fetch;
}
-Andy
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-03-21 13:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-21 11:21 DBD-Pg prepared statement versus plain execution Rafael Martinez <r.m.guerrero@usit.uio.no>
2012-03-21 12:59 ` ktm@rice.edu <ktm@rice.edu>
2012-03-21 13:44 ` Andy Colson <andy@squeakycode.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox