OK...I added your trigger example as option 8. And I had pasted the wrong
version in my last email. My timings were correct. Your example also did
sql_exec instead of using prepare so it will run slower.
I also made this compilable on Unix too.
On Unix my timing matches the run time and there are the right # of records in
the database with the right values. Unix is faster than Windows (8-core 2.6Ghz
Unix system using SAS drives (I'm not sure of the model but I'm quite sure they
are 7200RPM).
Perhaps this is write caching on the disk?
time ./batch 1600 1 0
1543.1
real 0m1.042s
user 0m0.046s
sys 0m0.216s
Something tells me your theory is wrong. All 1600 records get inserted into
the database in approx 1 second so it matches independent timing quite well.
Here's my results with your trigger. I'll maintain the timings are correct
until somebody can prove this wrong. Your theory is simply incomplete and is
not taking into account SQLite's behavior.
D:\SQLite>batch 504 1 0 -- simple single insert no batch
442.1
D:\SQLite>batch 504 1 8 -- with trigger insert on 2nd table -- single row
307.3
D:\SQLite>batch 504 1 9 -- trigger + in memory
5378.5
D:\SQLite>batch 504 1 10 -- trigger + index
212.3
D:\SQLite>batch 504 1 12 -- trigger + WAL mode
2482.4
D:\SQLite>batch 504 1 14 -- trigger + WAL mode + index
461.0
D:\SQLite>batch 504 1 6 -- WAL mode + index
4608.0
D:\SQLite>batch 504 504 6 -- WAL mode + index in one transaction
32256.0
Of course you can't do your trigger (i.e. update) inside a transaction so
there's some error checking for that now.
You trigger is a factor of 10 slower. I think we determined before that doing
that update directly instead of a trigger would be faster. I should add a
method for that too.
I also added a SYNCHRONOUS=OFF option#16
That speeds it up a bit too. I didn't see where FULL made much of a difference
here so I just made OFF the option.
D:\SQLite>batch 1600 1 0
414.6
D:\SQLite>batch 1600 1 0
279.8
D:\SQLite>batch 1600 1 16
602.4
D:\SQLite>batch 1600 1 16
572.1
D:\SQLite>batch 1600 1600 16
102400.0
#include <stdio.h>
#include <stdlib.h>
#ifdef _WIN32
#include <windows.h>
#include <winbase.h>
#else
#include <sys/time.h>
#endif
#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 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;
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 a trigger\n");
fprintf(stderr,"Flag 16 = 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) {
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 & 16) {
rc=sqlite3_exec(db,"pragma synchronous=OFF",NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
}
if (flags & 2) {
rc=sqlite3_exec(db,"create index tx on t (i)",NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
}
if (flags & 4) {
rc=sqlite3_exec(db,"pragma journal_mode=WAL",NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
}
if (flags & 8) {
AddTrigger(db);
if (interval != 1) {
fprintf(stderr,"Can't do trigger and begin/commit together\n");
exit(-1);
}
}
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\n",nrec/(elapsed()-t1));
sqlite3_close(db);
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
