On the MC55 and MC70 we use with Sqlite 3.5.9: PRAGMA temp_store = MEMORY PRAGMA journal_mode = PERSIST PRAGMA journal_size_limit = 500000
On 2/16/2011 5:24 AM, Black, Michael (IS) wrote: > Try this benchmark program and see what numbers you get. You need to compare > to other machines with the same benchmark to see if it's the machine or your > programming/architecture. > The MC55 is a 520Mhz PXA270 so I would expect to see more than a 6X > difference from my 3Ghz box (memory speed is no doubt slower too). > > batch 10000 10 0 > Sqlite Version: 3.7.5 > Inserting 10000 rows using a bulk of 10 > commits per second: 14217.7 > batch 10000 10 4 > using wal mode > Sqlite Version: 3.7.5 > Inserting 10000 rows using a bulk of 10 > commits per second: 44952.5 > batch 10000 10 6 > using index on t(i) > using wal mode > Sqlite Version: 3.7.5 > Inserting 10000 rows using a bulk of 10 > commits per second: 42383.5 > batch 10000 10 7 > using mode: :memory: > using index on t(i) > using wal mode > Sqlite Version: 3.7.5 > Inserting 10000 rows using a bulk of 10 > commits per second: 219279.0 > > #include<stdlib.h> > #include<stdio.h> > #include<string.h> > #ifdef _WIN32 > #include<windows.h> > #include<winbase.h> > #include<sys/timeb.h> > #else > #include<sys/time.h> > #endif > #include<time.h> > #include "sqlite3.h" > double elapsed() > { > #ifdef _WIN32X > struct _timeb timebuffer; > _ftime(&timebuffer ); > return( (double)timebuffer.time + timebuffer.millitm / 1000.0); > #else > double t1; > struct timeval tv; > static long base_seconds; > gettimeofday(&tv,NULL); > if (base_seconds==0) base_seconds=tv.tv_sec; > return (tv.tv_sec-base_seconds)+tv.tv_usec/(double)1000000; > #endif > } > 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+1;"); > 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 flags=0; > double t1; > char SqlTxt[256]; > if (argc != 4) { > fprintf(stderr,"Usage: %s nrecords commit_interval flags\n",argv[0]); > fprintf(stderr,"Flag 0 = simple table\n"); > fprintf(stderr,"Flag 1 = in-memory database\n"); > fprintf(stderr,"Flag 2 = add index\n"); > fprintf(stderr,"Flag 4 = WAL mode\n"); > fprintf(stderr,"Flag 8 = Add an update trigger per insert\n"); > fprintf(stderr,"Flag 16= Add a manual update per insert\n"); > fprintf(stderr,"Flag 32 = Synchronous=Off\n"); > fprintf(stderr,"Add flags to combine features\n"); > exit(-1); > } > nrec=atoi(argv[1]); > interval=atoi(argv[2]); > flags=atoi(argv[3]); > if (flags& 1) > { > puts("using mode: :memory:"); > rc=sqlite3_open(":memory:",&db); > } > else { > remove("batch.db"); > rc=sqlite3_open("batch.db",&db); > } > if (rc != SQLITE_OK) { > puts(sqlite3_errmsg(db)); > } > rc=sqlite3_exec(db,"create table t (i integer)",NULL,NULL,NULL); > if (rc != SQLITE_OK) { > puts(sqlite3_errmsg(db)); > } > rc=sqlite3_exec(db,"create table t2 (n integer)",NULL,NULL,NULL); > if (rc != SQLITE_OK) { > puts(sqlite3_errmsg(db)); > } > rc=sqlite3_exec(db,"insert into t2 values(0)",NULL,NULL,NULL); > if (rc != SQLITE_OK) { > puts(sqlite3_errmsg(db)); > } > if (flags& 32) { > rc=sqlite3_exec(db,"pragma synchronous=OFF",NULL,NULL,NULL); > if (rc != SQLITE_OK) { > puts(sqlite3_errmsg(db)); > } > puts("using pragma synchronous=OFF"); > } > if (flags& 2) { > rc=sqlite3_exec(db,"create index tx on t (i)",NULL,NULL,NULL); > if (rc != SQLITE_OK) { > puts(sqlite3_errmsg(db)); > } > puts("using index on t(i)"); > } > if (flags& 4) { > rc=sqlite3_exec(db,"pragma journal_mode=WAL",NULL,NULL,NULL); > if (rc != SQLITE_OK) { > puts(sqlite3_errmsg(db)); > } > puts("using wal mode"); > } > if (flags& 8) { > AddTrigger(db); > puts("using update trigger"); > /** if (interval != 1) { > fprintf(stderr,"Can't do trigger and begin/commit together\n"); > exit(-1); > } > **/ > } > if( flags& 16 ) > puts("using manual update after insert"); > printf("Sqlite Version: %s\n", sqlite3_version); > printf("Inserting %d rows using a bulk of %d\n", nrec, interval); > sqlite3_prepare_v2(db,"insert into t values(?)",-1,&stmt,NULL); > t1=elapsed(); > 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); > } > sqlite3_bind_int(stmt,1,n); > rc = sqlite3_step(stmt); > if (rc != SQLITE_DONE) { > puts(sqlite3_errmsg(db)); > } > sqlite3_reset(stmt); > if( flags& 16 ) > { > sprintf(SqlTxt, "UPDATE t2 SET n = %d;", n+1); > sqlite3_exec(db, SqlTxt,NULL,NULL,NULL); > } > } > if (interval != 1) > sqlite3_exec(db,"commit",NULL,NULL,NULL); > printf("commits per second: %.1f\n",nrec/(elapsed()-t1)); > sqlite3_close(db); > } > > > 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 sasikuma...@tcs.com [sasikuma...@tcs.com] > Sent: Wednesday, February 16, 2011 5:13 AM > To: sqlite-users@sqlite.org > Subject: EXT :[sqlite] Performance Problem > > Hi, > > I'm using Motorola MC55 device, with 2GB external memory card. > > For the SQlite Db I have used the following Pragma values > > PRAGMA cache_size = 16000 > PRAGMA temp_store = 2 > PRAGMA synchronous = OFF > PRAGMA locking_mode = EXCLUSIVE > > for some performance improvement > > For insertion of records I use > > sqlite3_prepare > > then binding the values using > > sqlite3_bind_int64 > > and finally executing using > > sqlite3_step > > We use to insert a set of 10 records every time, so I use "BEGIN" and > "COMMIT" transaction . > > Despite these the insertion is slow, like it takes around 700 milliseconds > to insert one record, is there anyother way to improve the performance. > Kindly help in this regard > > Regards, > Sasikumar U > =====-----=====-----===== > Notice: The information contained in this e-mail > message and/or attachments to it may contain > confidential or privileged information. If you are > not the intended recipient, any dissemination, use, > review, distribution, printing or copying of the > information contained in this e-mail message > and/or attachments to it are strictly prohibited. If > you have received this communication in error, > please notify us by reply e-mail or telephone and > immediately and permanently delete the message > and any attachments. Thank you > > > _______________________________________________ > 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