Re: Parsing speed (was Re: pgstats_initstats() cost)

Gavin Sherry <swm@linuxworld.com.au> writes:
> On Tue, 12 Aug 2003, Tom Lane wrote:
>> I'm beginning to think that was a serious omission.  I'm tempted to fix
>> it, even though we're past feature freeze for 7.4.  Comments?

> Can you give an example of this usage of this API? I am wondering whether
> releasing this specific feature would be eclipsed by a generalised bound
> variables solution in a future release... still, that's a nice speed up
> :-).

Attached is the test code I was using to compare speeds.  It won't do
you much good without the accompanying libpq mods, but it's enough to
illustrate the usage.  (This is a hacked version of example program 3 from
the 7.4 libpq docs.)

        regards, tom lane


#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>


static void
exit_nicely(PGconn *conn)
{PQfinish(conn);exit(1);
}

int
main(int argc, char **argv)
{const char *conninfo;PGconn       *conn;PGresult   *res;const char *paramValues[3];int tries, ntries;
/* * If the user supplies a parameter on the command line, use it as the * conninfo string; otherwise default to
settingdbname=template1 and * using environment variables or defaults for all other connection * parameters. */if (argc
>1)    conninfo = argv[1];else    conninfo = "dbname = template1";
 
if (argc > 2)    ntries = atoi(argv[2]);else    ntries = 10;
/* Make a connection to the database */conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */if (PQstatus(conn) != CONNECTION_OK){
fprintf(stderr,"Connection to database '%s' failed.\n", PQdb(conn));    fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);}
res = PQexec(conn, "PREPARE mystmt(text,int,float8) AS insert into abc values($1,$2,$3)");if (PQresultStatus(res) !=
PGRES_COMMAND_OK){   fprintf(stderr, "PREPARE failed: %s", PQerrorMessage(conn));    PQclear(res);
exit_nicely(conn);}PQclear(res);
res = PQexec(conn, "BEGIN");if (PQresultStatus(res) != PGRES_COMMAND_OK){    fprintf(stderr, "BEGIN failed: %s",
PQerrorMessage(conn));   PQclear(res);    exit_nicely(conn);}PQclear(res);
 
for (tries = 0; tries < ntries; tries++){
#if 0res = PQexec(conn, "insert into abc values('joe''s place',22,123.4)");
#endif

#if 0/* Here are our out-of-line parameter values */paramValues[0] = "joe's place";paramValues[1] = "22";paramValues[2]
="123.4";
 
res = PQexecParams(conn,                   "insert into abc values($1,$2,$3)",                   3,        /* 3 params
*/                  NULL,    /* let the backend deduce param type */                   paramValues,
NULL,   /* don't need param lengths since text */                   NULL,    /* default to all text params */
       1);        /* ask for binary results */
 
#endif

#if 1/* Here are our out-of-line parameter values */paramValues[0] = "joe's place";paramValues[1] = "22";paramValues[2]
="123.4";
 
res = PQexecPrepared(conn,                   "mystmt",                   3,        /* 3 params */
paramValues,                  NULL,    /* don't need param lengths since text */                   NULL,    /* default
toall text params */                   1);        /* ask for binary results */
 
#endif
if (PQresultStatus(res) != PGRES_COMMAND_OK){    fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
PQclear(res);   exit_nicely(conn);}
 
PQclear(res);}
res = PQexec(conn, "COMMIT");if (PQresultStatus(res) != PGRES_COMMAND_OK){    fprintf(stderr, "COMMIT failed: %s",
PQerrorMessage(conn));   PQclear(res);    exit_nicely(conn);}PQclear(res);
 
/* close the connection to the database and cleanup */PQfinish(conn);
return 0;
}