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 <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
int
main()
{
clock_t t1;
clock_t t2;
int i;
int stat;
sqlite3 *db;
char *errmsg = 0;
sqlite3_open("theDB", &db);
(void) sqlite3_exec(db, "create table notes(subject)", 0, 0, &errmsg);
// time the additions
t1 = clock();
// add some rows
(void) sqlite3_exec(db, "BEGIN", 0, 0, &errmsg);
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, &errmsg);
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, &errmsg);
t2 = clock();
sqlite3_close(db);
printf("added %d records to notes\n", i - 1);
printf("elapsed time: %d\n", (t2 - t1));
}
