Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[Reid Thompson]

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  - Determine processor time 


*SYNOPSIS*
  *#include *

  *clock_t clock (void);*

*DESCRIPTION*
  The *clock () *function returns an 
approximation of processor time  used by
  the program.

*RETURN VALUE*
  The value returned is the CPU time  
used so far as  a *clock_t*; to get the
  number   of  seconds  used,  
divide by *CLOCKS_PER_SEC*.   If the 
processor
  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 
#include 
#include 
#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 ();
 (void) gettimeofday (, 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 ();
 (void) gettimeofday (, 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;
}

Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[Reid Thompson]

dan greene wrote:

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???


Plase help!!!??

because w/o the begin-end/commit every insert must be committed to disk 
before the next insert begins.  Within a transaction( between the begin 
and end/commit ), all the inserts are 'posted', but there is only ONE 
commit at the the end.  All RDBMS systems will perform multiple 
consecutive db actions as you've described
within a transaction faster than they will without.  As previously 
noted, the time is spend syncing the data to your drive.


Results using mod/hack of your post(see below), Windows XP, athlon 
2500XP, 512MB RAM in CYGWIN..


$ ./a
added 200 records to notes
elapsed time: 265
cyberhome: /home/rthompso>
$ vi sqlitetest.c
cyberhome: /home/rthompso>
$ gcc -L/usr/local/lib  sqlitetest.c -lsqlite3
cyberhome: /home/rthompso>
$ ./a
added 200 records to notes
elapsed time: 15

The vi between the two invocations was to add the BEGIN/COMMIT calls.

$ cat sqlitetest.c
#include 
#include 
#include 
#include 
int
main()
{
   clock_t t1;
   clock_t t2;
   int i;
   int stat;
   sqlite3 *db;
   char *errmsg = 0;

   sqlite3_open("theDB", );
   (void) sqlite3_exec(db, "create table notes(subject)", 0, 0, );
   // time the additions
   t1 = clock();
   // add some rows
   (void) sqlite3_exec(db, "BEGIN", 0, 0, );
   for (i = 1; i < 201; i++)
   {
   char *statement;
   char buffer[50];
   sprintf(buffer, "my subject%d", i);
   statement =
   sqlite3_mprintf("insert into notes(subject) values('%q');", 
buffer);

   stat = sqlite3_exec(db, statement, 0, 0, );
   if (stat != SQLITE_OK)
   {
   printf("insert error at i=%1d: %s\n", i, errmsg);
   sqlite3_free(errmsg);
   break;
   }

   sqlite3_free(statement);
   }   // for
   (void) sqlite3_exec(db, "COMMIT", 0, 0, );

   t2 = clock();
   sqlite3_close(db);
   printf("added %d records to notes\n", i - 1);
   printf("elapsed time: %d\n", (t2 - t1));
}

Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[Eugene Wee]

Hi Dan,

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
It doesnt take anywhere near that long when you wrap the inserts in a 
transaction, right?


> And, why does adding a begin: / end:commit: around the whole loop speed
> up the operations so drastically???
You need to read the wiki:
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning

Eugene Wee

Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[dan greene]

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???


Plase help!!!??