> 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).
that's why I added that in your test code... :-)
Surprisingly I'm not able to reproduce a dramatic slowdown using my
simple trigger test. It does slow down to use a trigger compared to
a manual update but not more than natural, but I'm not sure
if my trigger test is a working use-case.
Wal mode speeds up noticable. That is exiting.
Marcus
>
> 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
