How many roundtrips does pg_query_params use?

Two part question.

1. As per the title, how many roundtrips does pg_query_params use?
2. How do I count the number of database roundtrips in my scripts?

Example code:

        $result = pg_query_params($pg_connection, <<<'EOQ'
INSERT 开发者_如何学编程INTO accounts (username, salt, hash, created, lastlogin)
    VALUES ($1, $2, $3, NOW(), NOW())
EOQ
                    , array($username, $salt, crypt($password, $salt)));

Thanks.

pg_query_params() uses only one roundtrip. It's a feature of the postgres wire protocol which allows to send query and params separately but without having to wait for a reply between the two.

You cannot count roundtrips really, but you could look at the source code, or simply look at query time while querying a server with non-zero ping time.

FYI, on a postgres server running on localhost, using a real prepared statement (ie, PDO) is much slower (>2x...) on very simple queries (because it uses 2 roundtrips) than pg_query_params().

About prepared statements : Postgres has several ways to execute a query.

• Raw SQL with embedded parameters (the oldskool way) : it's still useful, and the only way to construct IN() or VALUES() lists. A proper API layer that handles quoting is a must (a la python DBAPI). Any trace of a direct call to quote() in user code is a sign of trouble !

• Single use prepared statements (pg_query_params) : that's a special case of the protocol where the "Prepare" and "Execute with parameters" messages are sent in a single packet. From the user's point of view it's about the same speed as raw SQL (one roundtrip), a bit less parsing overhead on the server (might be useful to INSERT large data), and automatically secure (no quoting). You need to replace IN() and VALUES() lists with arrays (=ANY, unnest()), though. Postgres will use the parameter values in the query plan, so you'll get good plans.

• Real prepared statements (PDO, prepare, etc) : Those are good if you want to pay the planning cost once and execute many times (although, usually a SQL query inside a loop is a mistake). Two roundtrips, much slower for a simple SELECT on primary key. Can hog postgres memory if you prepare a zillion plans without deallocating them afterwards. Parameters usually are NOT used in the query planning (since they are not available then) so you might get non optimal query plans. In the end, this is rather useless for a PHP application where neither Postgres nor PHP will cache the query plans. If you use an application server which keeps cached query plans for common simple queries, then a prepared simple query (like a SELECT on primary key) will use much less server CPU than a non-prepared one (quite faster than the self-proclaimed speed king MyISAM actually).