A note; I was testing this script with the lastest sqlite3.exe sqlite3 test.db < sql.log
the selects are really fast; the inserts are REALLY slow. My own code I use get_autocommit but never set_autocommit(if there is such a thing)... otherwise I'm using sqlite3_open_v2; sqlite3_prepare_v2, step, and finalize.... so pretty vanilla; would expect sqlite3 to not be much smarter; and it performs just as badly as my own code. On Sun, Aug 13, 2017 at 11:00 PM, J Decker <d3c...@gmail.com> wrote: > (Sent to wrong address to start; forwarded here(?)) > > ---------- Forwarded message ---------- > From: J Decker <d3c...@gmail.com> > Date: Sun, Aug 13, 2017 at 10:57 PM > Subject: How can I make this faster? > To: General Discussion of SQLite Database <sqlite-us...@sqlite.org> > > > So I have this sql script that inserts into a single table, and it is VERY > slow. > it is even slower without journal_mode=WAL > > Also, is a unique constraint also an index? > > if I use this in my memory mapped VFS it is MUCH faster (but it doesn't > supply mmap, so it's only using -journal that it creates and deletes a lot). > > The following is a snippet of this much longer script > https://drive.google.com/open?id=0B812EYiKwtkkSVBUVWtpYUR4WW8 > > it's only 1687 lines and about 50% are select statements, I would expect > this to complete in under 1 second.not 22 seconds. > > (the command should also be replace into, not insert into; but given the > constraint, I wouldn't think that will matter) > ------- > > #PRAGMA journal_mode=WAL;; > select tbl_name,sql from sqlite_master where type='table' and > name='record'; > create table `record` (`soul` char,`field` char,`value` char,`relation` > char,`state` char,CONSTRAINT `record_unique` UNIQUE (`soul`,`field`) ON > CONFLICT REPLACE); > create index if not exists soul_index on record(soul); > create index if not exists soul_field_index on record(soul,field); > select * from Record where soul='db'; > select state from Record where soul='db' and field='hello'; > insert into Record (soul,field,value,relation,state) > values('db','hello','"world"',NULL,1502689508373); > select state from Record where soul='db' and field='other'; > insert into Record (soul,field,value,relation,state) > values('db','other','"test"',NULL,1502689508408); > select state from Record where soul='j6bqj9ec1JUbWzaheTEO' and > field='field'; > insert into Record (soul,field,value,relation,state) > values('j6bqj9ec1JUbWzaheTEO','field','"randomkey"',NULL,150 > 2689508436.001); > select state from Record where soul='db' and field='j6bqj9ec1JUbWzaheTEO'; > insert into Record (soul,field,value,relation,state) > values('db','j6bqj9ec1JUbWzaheTEO',NULL,'j6bqj9ec1JUbWzaheTE > O',1502689508466); > select * from Record where soul='j6bqj9ec1JUbWzaheTEO'; > select state from Record where soul='j6bqj9fz01aP8aM1rLy7R3U' and > field='field'; > insert into Record (soul,field,value,relation,state) > values('j6bqj9fz01aP8aM1rLy7R3U','field','"randomkey"',NULL, > 1502689508495.002); > select state from Record where soul='db' and field='j6bqj9fz01aP8aM1rLy7R3U > '; > insert into Record (soul,field,value,relation,state) > values('db','j6bqj9fz01aP8aM1rLy7R3U',NULL,'j6bqj9fz01aP8aM1rLy7R3U',15026 > 89508528); > select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U'; > select state from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi' and > field='field'; > insert into Record (soul,field,value,relation,state) > values('j6bqj9ho02jCZpwwI9kz4Vi','field','"randomkey"',NULL, > 1502689508556.003); > select state from Record where soul='db' and field='j6bqj9ho02jCZpwwI9kz4Vi > '; > insert into Record (soul,field,value,relation,state) > values('db','j6bqj9ho02jCZpwwI9kz4Vi',NULL,'j6bqj9ho02jCZpwwI9kz4Vi',15026 > 89508611); > select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi'; > select state from Record where soul='j6bqj9kgu96XPzRp4HfD' and > field='field'; > insert into Record (soul,field,value,relation,state) > values('j6bqj9kgu96XPzRp4HfD','field','"randomkey"',NULL,150 > 2689508656.001); > select * from Record where soul='j6bqj9ec1JUbWzaheTEO'; > select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U'; > select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi'; > select * from Record where soul='j6bqj9kgu96XPzRp4HfD'; > > ---- > > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
