We already determined that triggers kill you on inserts so you can't use
triggers (other than as another example of what NOT to do for speed).
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Marcus Grimm [mgr...@medcom-online.de]
Sent: Saturday, February 12, 2011 10:23 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
> On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS)
> <michael.bla...@ngc.com
>> 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
