I fixed a couple of bugs in my program...I had converted from clock() to the
more appropriate gettimeofday and forgot to remove the CLOCKS_PER_SEC factor
(what's a few order of magnitude between friends :-). Plus I added a 3rd
argument so you can in-memory, index, and WAL mode too (or combine them).
I should've realized it wasn't running this fast but the small 5000 record size
got me.
Test it yourself.
I do have a 7200RPM drive. My 261.4 numer is still 2+X your theoretical.
batch 5000000 5000000 0
274207.4
batch 5000000 5000000 1 -- memory
540540.5
batch 5000000 5000000 2 -- w/index
160481.4
batch 5000000 5000000 3 -- memory+index
220689.7
batch 5000000 5000000 4 -- WAL mode
441989.0
batch 5000000 5000000 5 -- WAL mode+memory
541455.2
batch 5000000 5000000 6 -- WAL mode+index
188902.0
batch 5000000 5000000 7 -- WAL mode+index+memory
219478.7
And doing the 5000 record example and testing commit intervals
batch 5000 5000 0
320000.0
batch 5000 2500 0
320000.0
batch 5000 1200 0
160000.0
batch 5000 500 0
160000.0
batch 5000 200 0
80000.0
batch 5000 100 0
35555.6
batch 5000 500 0
160000.0
batch 5000 50 0
20000.0
batch 5000 25 0
11034.5
batch 5000 12 0
5333.3
batch 5000 6 0
2461.5
batch 5000 3 0
682.3
batch 5000 2 0
509.6
batch 5000 1 0
261.4
#include <stdio.h>
#include <windows.h>
#include <winbase.h>
#include <time.h>
#include "sqlite3.h"
double elapsed()
{
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;
}
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;
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,"Add flags to combine features\n");
exit(-1);
}
nrec=atoi(argv[1]);
interval=atoi(argv[2]);
flags=atoi(argv[3]);
if (flags & 1) {
rc=sqlite3_open(":memory:",&db);
}
else {
remove("batch.db");
rc=sqlite3_open("batch.db",&db);
}
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
sqlite3_exec(db,"create table t (i integer)",NULL,NULL,NULL);
if (flags & 2) {
sqlite3_exec(db,"create index tx on t (i)",NULL,NULL,NULL);
}
if (flags & 4) {
sqlite3_exec(db,"pragma journal_mode=WAL",NULL,NULL,NULL);
}
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);
}
sqlite3_exec(db,"commit",NULL,NULL,NULL);
//printf("%.1f inserts per sec\n",nrec/((elapsed()-t1)/CLOCKS_PER_SEC));
printf("%.1f\n",nrec/((elapsed()-t1)/CLOCKS_PER_SEC));
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 Jim Wilcoxson [pri...@gmail.com]
Sent: Saturday, February 12, 2011 10:11 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:
> Here's a little benchmark program I wrote to test a super-simple
> one-integer insert to test sql speed and commit interval behavior.
>
> Running it on my machine (Windows XP64 8-core 3Ghz gcc (tdm64-1) 4.5.1) I
> go from 320M inserts per second to 361K inserts per second when no
> begin/commit occurs. With WAL mode turned on it only drops to 5.9M inserts
> per second.
>
> D:\SQLite>batch 5000 5000
> 320000000.0 inserts per sec
> D:\SQLite>batch 5000 2500
> 320000000.0 inserts per sec
> D:\SQLite>batch 5000 1200
> 160000000.0 inserts per sec
> D:\SQLite>batch 5000 600
> 160000000.0 inserts per sec
> D:\SQLite>batch 5000 300
> 106666666.7 inserts per sec
> D:\SQLite>batch 5000 150
> 53333333.3 inserts per sec
> D:\SQLite>batch 5000 75
> 32000000.0 inserts per sec
> D:\SQLite>batch 5000 40
> 17777777.8 inserts per sec
> D:\SQLite>batch 5000 20
> 10000000.0 inserts per sec
> D:\SQLite>batch 5000 10
> 3333333.3 inserts per sec
> D:\SQLite>batch 5000 5
> 2406015.0 inserts per sec
> D:\SQLite>batch 5000 2
> 526315.8 inserts per sec
> D:\SQLite>batch 5000 1
> 360766.6 inserts per sec
>
Unless I'm missing something, SQLite has to update the first page of the
database on every commit, to update the change counter. Assuming you are
using rotating media, that record can only be updated 120 times per second,
maximum, on a 7200RPM drive.
I don't understand how you can do 360K commits per second if your system is
actually doing "to the platter" writes on every commit. Can someone clue me
in?
Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
_______________________________________________
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
