From: | Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | DBD-Pg prepared statement versus plain execution |
Date: | 2012-03-21 11:21:23 |
Message-ID: | 4F69B9B3.7030303@usit.uio.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Rafael Martinez | 2012-03-21 11:42:00 | timing != log duration |
Previous Message | Kevin Grittner | 2012-03-19 16:45:56 | Re: slow self-join query |