So, if I'm reading your message right, the 25 seconds for inserting
200 (that is only 200 messsages) into a database under win 2000 is
correct???
Perhaps it's just my uneducated opinion but your explanation sounds
bogus... Why would the folks who wrote SQLITE show it off as being
so fast if, under win 2000, it only added 200 records every 25
seconds????
And, why does adding a begin: / end:commit: around the whole loop
speed up the operations so drastically???
just realized also, clock() is probably NOT what you want to use for
your test.
You probably want to use time() or gettimeofday().
Clock determines processor time used:
*NAME*
clock <http://seth.positivism.org/man.cgi/clock> - Determine processor time
<http://seth.positivism.org/man.cgi/time>
*SYNOPSIS*
*#include <time.h>*
*clock_t clock <http://seth.positivism.org/man.cgi/clock>(void);*
*DESCRIPTION*
The *clock <http://seth.positivism.org/man.cgi/clock>() *function returns an
approximation of processor time <http://seth.positivism.org/man.cgi/time> used by
the program.
*RETURN VALUE*
The value returned is the CPU time <http://seth.positivism.org/man.cgi/time>
used so far as <http://seth.positivism.org/man.cgi/1/as> a *clock_t*; to get the
number <http://seth.positivism.org/man.cgi/6/number> of seconds used,
divide by *CLOCKS_PER_SEC*. <http://seth.positivism.org/man.cgi/1/builtins> If the
processor
time <http://seth.positivism.org/man.cgi/time> used is not available or
its value cannot be represented, the
function returns the value (clock_t)-1.
As info -- a correlating test with
---------------------------------------------------------------------------
PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3
on a Dell laptop 256MB RAM, hdparm -W0 ( write caching off).
dmesg | grep processor => Detected 697.025 MHz processor.
$ ./a.out
added 200 records to notes
time1: 1126994873 time2: 1126994876
elapsed time: 3
seconds: 3 end_microsecs: 813272 start_microsecs 905119 microsecs -91847
..takes between 3 and 4 seconds
Second run wrapped in transaction:
$ ./a.out
added 200 records to notes
time1: 1126994915 time2: 1126994915
elapsed time: 0
seconds: 0 end_microsecs: 237435 start_microsecs 180558 microsecs 56877
..takes 56877 microseconds
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include "libpq-fe.h"
static void
exit_nicely (PGconn * conn)
{
PQfinish (conn);
exit (1);
}
int
main (int argc, char **argv)
{
time_t t1;
time_t t2;
struct timeval tb1;
struct timeval tb2;
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields;
int i;
long t_diff;
conninfo = "dbname = test";
/* 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 failed: %s",
PQerrorMessage (conn));
exit_nicely (conn);
}
res = PQexec (conn, "drop table notes");
if (PQresultStatus (res) != PGRES_COMMAND_OK)
{
fprintf (stderr, "drop table command failed: %s",
PQerrorMessage (conn));
//PQclear(res);
//exit_nicely(conn);
}
PQclear (res);
res = PQexec (conn, "create table notes(subject text)");
if (PQresultStatus (res) != PGRES_COMMAND_OK)
{
fprintf (stderr, "create table command failed: %s",
PQerrorMessage (conn));
PQclear (res);
exit_nicely (conn);
}
PQclear (res);
/* comment out BEGIN txn
res = PQexec (conn, "BEGIN");
if (PQresultStatus (res) != PGRES_COMMAND_OK)
{
fprintf (stderr, "BEGIN command failed: %s", PQerrorMessage (conn));
PQclear (res);
exit_nicely (conn);
}
PQclear (res);
*/
(void) time (&t1);
(void) gettimeofday (&tb1, NULL);
for (i = 1; i < 201; i++)
{
char statement[1024];
char buffer[50];
sprintf (buffer, "my subject%d", i);
sprintf (statement, "%s %s%s", "insert into notes(subject) values('",
buffer, "')");
res = PQexec (conn, statement);
if (PQresultStatus (res) != PGRES_COMMAND_OK)
{
fprintf (stderr, "insert command failed: %s",
PQerrorMessage (conn));
PQclear (res);
exit_nicely (conn);
}
PQclear (res);
}
/* comment out COMMIT txn
res = PQexec (conn, "COMMIT");
PQclear (res);
*/
(void) time (&t2);
(void) gettimeofday (&tb2, NULL);
printf ("added %d records to notes\n", i - 1);
printf ("time1: %d time2: %d\n", t1, t2);
printf ("elapsed time: %d\n", (t2 - t1));
t_diff = (long) ((tb2.tv_sec - tb1.tv_sec) + (tb2.tv_usec - tb1.tv_usec));
printf("seconds: %ld end_microsecs: %ld start_microsecs %ld
microsecs %ld\n",
(tb2.tv_sec - tb1.tv_sec), tb2.tv_usec, tb1.tv_usec,
tb2.tv_usec - tb1.tv_usec);
/* close the connection to the database and cleanup */
PQfinish (conn);
return 0;
}
