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
