How to measure time - Mailing list pgsql-cygwin

Hi!

I wanted to measure the time for how long
PostreSQL would take to fulfill a query
under Windows2000, so I installed
Cygwin, G++ and libpqxx.

Once I let the test run (I don't get any error
message), I receive different results with
significant differences.

Sometimes I get for the query 0.0 milliseconds, another
time 1.16e-23 milliseconds and the third option is
16 milliseconds.

However, when I let the query run in pgAdmin III
I get about 96 seconds?!

My question is how can I measure the time for a query
effectively? Where do I make the error?

Below is my source code.

Thank you very much.

Fernando Santini

/*
 * PostGreSQL -> SQBM
*/
 #include <stdio.h>
 #include <libpq-fe.h>
 #include <iostream>
 #include <all.h>
 #include <time.h>
 using namespace pqxx;

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

 main()
 {
     char       *pghost,
                *pgport,
                *pgoptions,
                *pgtty;
     char       *dbName;
     int         nFields;
     int         i,
                 j;
     double time1=0.0;
     double tstart;
     /* FILE *debug; */
     PGconn     *conn;
     PGresult   *res;
     pghost = NULL;
     pgport = NULL;
     pgoptions = NULL;
     pgtty = NULL;
     dbName = "tpch";

     conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);     /* Connection
to database */

     if (PQstatus(conn) == CONNECTION_BAD) /*Connection successfully established*/
     {
         fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
         fprintf(stderr, "%s", PQerrorMessage(conn));
         exit_nicely(conn);
     }

//--------------------------------------------------------------------------------------------------------------
//The interesting part

     std::cout << "--------------------------------------------------------"<<std::endl;

     std::cout << " Query 14:" << std::endl;

     res = PQexec(conn, "BEGIN");
     if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         fprintf(stderr, "BEGIN command failed\n");
         PQclear(res);
         exit_nicely(conn);
     }
     PQclear(res);


      tstart = clock();
     /*fetch, select*/
     res = PQexec(conn, "select    100.00 * sum(case when ptype like 'PROMO%'
then lextendedprice * (1 - ldiscount) else 0 end) / sum(lextendedprice *
(1 - ldiscount)) as promorevenue from lineitem,    part where lpartkey = ppartkey
and lshipdate >= date '1993-11-01' and lshipdate < date '1993-12-01'");
     time1 += clock() - tstart;

     /* attributes */
     nFields = PQnfields(res);

     for (i = 0; i < nFields; i++)
         printf("%-15s", PQfname(res, i));
     printf("\n\n");
     /* tuples (selected) */
     for (i = 0; i < PQntuples(res); i++)
     {
         for (j = 0; j < nFields; j++)
             printf("%-15s", PQgetvalue(res, i, j));
         printf("\n");
     }
     PQclear(res);

     std::cout << " Query 14 zeit = " << time1 << " millisekunden " << std::endl;
     time1 = time1/CLOCKS_PER_SEC;
     std::cout << " Query 14 zeit = " << time1 << " sekunden " << std::endl;
      std::cout << "--------------------------------------------------------"<<std::endl;


     /* commit */
     res = PQexec(conn, "COMMIT");
     PQclear(res);

//--------------------------------------------------------------------------------------------------------------
//The interesting part

     PQfinish(conn);
     return 0;
 }


ADSL - Gratis und so sicher wie noch nie
http://internet.sunrise.ch/de/internet/int_ads.asp