Skip site navigation (1) Skip section navigation (2)

Re: DBD-Pg prepared statement versus plain execution

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: DBD-Pg prepared statement versus plain execution
Date: 2012-03-21 13:44:29
Message-ID: 4F69DB3D.9000501@squeakycode.net (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2012-03-21 14:11:22
Subject: Re: timing != log duration
Previous:From: ktm@rice.eduDate: 2012-03-21 12:59:30
Subject: Re: DBD-Pg prepared statement versus plain execution

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group