> On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS)
> <[email protected]
>> D:\SQLite>batch 5000 1
>> 360766.6 inserts per sec
>>
>
> Unless I'm missing something, SQLite has to update the first page of the
> database on every commit, to update the change counter. Assuming you are
> using rotating media, that record can only be updated 120 times per
> second,
> maximum, on a 7200RPM drive.
>
> I don't understand how you can do 360K commits per second if your system
> is
> actually doing "to the platter" writes on every commit. Can someone clue
> me
> in?
I think the time measuring is not correct as sqlite can't in fact
do a commit with more than appx. 10-20 commits/sec.
here is a slightly modified version, ignore the trigger stuff:
--
#include <stdio.h>
#include <time.h>
#include <windows.h>
#include <winbase.h>
#include <time.h>
#include "sqlite3.h"
#include <sys/timeb.h>
#include <math.h>
int AddTrigger(sqlite3 *db)
{
char SqlTxt[256];
int rc;
strcpy(SqlTxt,"CREATE TRIGGER XX AFTER INSERT ON t FOR EACH ROW BEGIN
");
strcat(SqlTxt,"UPDATE t2 SET n = NEW.i;");
strcat(SqlTxt,"END;");
rc = sqlite3_exec(db,SqlTxt,NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
return(0);
}
int main(int argc, char *argv[])
{
sqlite3 *db;
sqlite3_stmt *stmt=NULL;
int rc;
int n=0;
int nrec=0;
int interval=0;
int AddTr = 0;
double t1;
char SqlTxt[256];
if (argc < 3) {
fprintf(stderr,"Usage: %s nrecords commit_interval\n",argv[0]);
exit(-1);
}
nrec=atoi(argv[1]);
interval=atoi(argv[2]);
if( argc == 4 )
AddTr = atoi(argv[3]);
remove("C:\\batch.db");
rc=sqlite3_open("C:\\batch.db",&db);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
sqlite3_exec(db,"create table t (i integer)",NULL,NULL,NULL);
sqlite3_exec(db,"create table t2 (n integer)",NULL,NULL,NULL);
sqlite3_exec(db,"insert into t2 values(7);",NULL,NULL,NULL);
if(AddTr)
AddTrigger(db);
// turn on WAL mode if you want to test it
//sqlite3_exec(db,"pragma journal_mode=WAL",NULL,NULL,NULL);
//sqlite3_prepare_v2(db,"insert into t values(?)",-1,&stmt,NULL);
t1=clock();
if (interval != 1) rc=sqlite3_exec(db,"begin",NULL,NULL,NULL);
while(n < nrec)
{
++n;
if (interval != 1 && (n% interval)==0) {
sqlite3_exec(db,"commit",NULL,NULL,NULL);
sqlite3_exec(db,"begin",NULL,NULL,NULL);
}
sprintf(SqlTxt, "insert into t values(%d);", n);
sqlite3_exec(db,SqlTxt,NULL,NULL,NULL);
}
sqlite3_exec(db,"commit",NULL,NULL,NULL);
printf("%.1f inserts per sec\n",nrec/((clock()-t1)/CLOCKS_PER_SEC));
sqlite3_close(db);
}
--
Marcus
>
> Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
