How to measure time

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