I just tested it, and it made no difference. The root cause of the problem is most likely not slow writes, because inserting duplicate values (which are ignored instead of written to disk) are just as slow.
But your suggestion may help me with another problem: when I fill an empty database with million of rows, they are written to disk in the order I inserted them, not in the order a VACUUM would put them (sequential per table). If I can force an atomic commit by enabling a journal_mode (MEMORY for example), I would possibly end up with much better structured database file. So your idea is appreciated, but for the problem reported in this thread, it had no effect. 2011/11/3 nobre <[email protected]> > > Have you tried setting journal_mode to the default DELETE option ? > Without atommic commits, maybe your inserts are going to disk one by one > instead of in a single step, when commiting your transactions, thus slowing > down disk writes. > > > Fabian-40 wrote: > > > > 2011/11/2 Black, Michael (IS) > > I do not use WAL, since I have turned 'journal_mode' off (to improve > > insert > > performance), and as far as I know WAL is only usefull when you need to > > keep a journal? > > > > I also have 'synchronous' off, so SQLite shouldn't be waiting for any > > filesystem flushes. I hoped it was writing all the newly inserted rows to > > disk using a single operation, as soon as I call 'commit'. But I observed > > it, and found out its already writing the rows as soon as soon as they > are > > inserted, not batching them for when I call commit. > > > > So that could be part of the problem. I don't have a real Windows machine > > at hand, so I will build one tomorrow, but if your expectations are > > correct, than it will be even slower than inside a virtual machine, > > because > > it will do individiual writes for the 1 million rows too, making > > performance even worse than it is now. > > > > Anothing thing is that I don't expect the slow performance have anything > > to > > do with slow disk writes, only with disk reads. I know this because when > I > > make the index UNIQUE, and try to insert 10.000 duplicate rows (which are > > all ignored), it has the same bad performance, even though there are zero > > bytes written to disk. So it points in the direction of the reads making > > it > > slow, not the writes. > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > View this message in context: > http://old.nabble.com/Slow-INDEX-tp32766886p32772266.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
