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