SOLVED!!
Marcus Grimm and I went back forth a number of times trying to figure out why
my benchmarks were so much faster than his.
Found it...
My SATA RAID setup had "Enable Advanced Performance" on by default (I had never
turned it on).
My secondary tests on an IDE drive showed similar performance to Marcus. Then
I turned on the performance mode for that one and got a 10X performance
improvement (advanced write caching...power loss susceptible...UPS needed if
you want reliability). The "Enable write caching" was already turned on.
To check your setup
My Computer/Properties on drive/Hardware/Properties on drive/Policies -- and
check "Enable advanced performance".
Here's the program we're benching with now. Now compilable on Unix, Windows
with VS 2008/2010/tdm-gcc 4.5.1.
Some results on a Western Digital 7200RPM IDE drive with advanced performance
turned on
F:\>d:batch 1000 1 0
Sqlite Version: 3.7.5
Inserting 1000 rows using a bulk of 1
commits per second: 166.7
F:\>d:batch 1000 1 4
using wal mode
Sqlite Version: 3.7.5
Inserting 1000 rows using a bulk of 1
commits per second: 1000.0
F:\>d:batch 500000 1000 0
Sqlite Version: 3.7.5
Inserting 500000 rows using a bulk of 1000
commits per second: 71428.6
F:\>d:batch 500000 1000 4
using wal mode
Sqlite Version: 3.7.5
Inserting 500000 rows using a bulk of 1000
commits per second: 166666.7
F:\>d:batch 500000 1000 5 << note that in-memory is less than 2X disk-based
now.
using mode: :memory:
using wal mode
Sqlite Version: 3.7.5
Inserting 500000 rows using a bulk of 1000
commits per second: 250000.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 _WIN32
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
