Re: How to measure time

Hey Fernando!

Well, I'm not surprised that pgAdmin III took so long. Remember,
pgAdmin III is just a GUI client frontend for PostgreSQL. It's purpose
is not so much speed as giving you a GUI to do basic things like
create/alter tables, etc....administration really.

As for the 0.0 milliseconds, wow! At that rate you could do anything
you wanted. ;-) Seriously, though, benchmarking is an ugly science that
always reminds me of the expression "There are lies, damn lies, and
statistics." Only I would add "...and then there are benchmarks." :-)

The time it takes a database to do something depends on a variety of
factors, from the performance of the box (CPU/RAM/HD spin
rate/blahblahblah) to what other processes were running and what they
were doing at the moment you ran the benchmark to a whole host of items.
If you want to know how long it takes PostgreSQL to fulfill a query,
it looks like you've done that...at least using C API calls. Now how
does that translate to doing the same query via PHP? It doesn't.

Now if your intention is to compare PostgreSQL running under Cygwin
running on Windows 2000 vs. say running on the same PC with Red Hat
Enterprise Linux 3, then your code, compiled for the two platforms,
MIGHT give you an idea. But again, there are so many factors involved
even assuming you use the exact same source code (compiler/linker
options, optimizations enabled/disabled, threading performance/settings
in OS, etc.), it's a bit of a crapshoot.

I guess the question you need to ask yourself is WHY are you wanting to
see how long it takes PostgreSQL to fulfill a query under Windows 2000?
Are you planning to use the same libpqxx and write a C program and
need to get a ballpark idea of just how many records you can
insert/modify/delete/etc. in a given amount of time? Then looks like
you're on the right track. Are you just wanting to be able to say
"PostgreSQL sucks on Windows but rulez on Linux"? Then that's another
story entirely. Now we're talking benchmarking as too many people use
it already. :-)

But as for the discrepancies in time of your code, between 0 & 16
milliseconds, that's not exactly a big deal. Again, it depends what you
are really after. If your intention is to do a single PGexec() call,
this isn't a bad test program. However, if your final code will execute
PGexec() within a loop and run thousands of times, then maybe you should
do the same here in your test suite. Then you see not just the time of
a single SQL statement executing, but also the total overhead in all the
C calls to PGexec(), which may or may not dwarf the actual SQL execution.

Just some thoughts.

fsantini(at)freesurf(dot)ch wrote:
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>