(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,1502689508436.001); select state from Record where soul='db' and field='j6bqj9ec1JUbWzaheTEO'; insert into Record (soul,field,value,relation,state) values('db',' j6bqj9ec1JUbWzaheTEO',NULL,'j6bqj9ec1JUbWzaheTEO',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',1502689508528); 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',1502689508611); 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,1502689508656.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
