> 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.
Yes, but that should be marginal.
When I send my code the trigger version wasn't finished.
The intension is to compare a trigger update vs. manual
UPDATE statement within your loop.
I was also quite alarmed about the issue recently raised
here about the performace penality a trigger introduces.
So far my experiments based on your code doesn't confirm
this: Using a trigger is slightly faster vs. manual update
if everthing is within implicit transaction only - Makes sense.
The trigger is slightly slower when compared to manual UPDATE
when using commit bulks - Here the overhead of the trigger
appears slightly higher than a manual UPDATE, but: The difference
is maybe 10% and not 6 times slower as reported. Strange...
Anyway, I would like to add my trigger test in your version
but I can't compile due to missing 'gettimeofday' function.
Are you using MSVS ? Can you advice me where you have the
gettimeofday from ?
Sqlite does use fsync() to sync the file content prior
proceeding with journal/db file updating. Some month ago
I looked into that and found it is done 3 times per
"INSERT". I'm not speaking about sector write performance it is
related to the file syncs,
I don't see whats wrong with my arguments, please advice.
Thanks for the code by the way - I like the approach to create
a little test tool to have a common basis to experiment.
Marcus
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
