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: [email protected] [[email protected]] on
> behalf of [email protected] [[email protected]]
> Sent: Wednesday, February 16, 2011 5:13 AM
> To: [email protected]
> 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
> [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
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
