Skip site navigation (1) Skip section navigation (2)

Re: How to measure time

From: Frank Seesink <frank(at)mail(dot)wvnet(dot)edu>
To: pgsql-cygwin(at)postgresql(dot)org
Subject: Re: How to measure time
Date: 2004-05-20 22:46:51
Message-ID: c8jch0$enq$1@sea.gmane.org (view raw or flat)
Thread:
Lists: pgsql-cygwin
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
> 


In response to

pgsql-cygwin by date

Next:From: Jason TishlerDate: 2004-05-21 12:00:29
Subject: Re: can you supply me installation guide on Windows
Previous:From: Mike GDate: 2004-05-18 01:56:44
Subject: Re: Initdb fails - semctl invalid argument

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group