So try creating your additional index, use the BEGIN/COMMIT, and let us know how it performs for you in comparison. I'll bet its faster even with the new index. You didn't say if you were already doing the BEGIN/COMMIT. Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 8:05 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Yes, I do use batch inserts. On 4/23/2010 8:56 AM, Black, Michael (IS) wrote: > > If insert speed is important are you doing batch inserts? > If so, you want to do a BEGIN/COMMIT to speed up your inserts a LOT. > Default action is to defer which mean no database locks occur during > your inserts. > http://www.sqlite.org/lang_transaction.html > > Michael D. Black > Senior Scientist > Northrop Grumman Mission Systems > > > ________________________________ > > From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs > Sent: Fri 4/23/2010 7:50 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Direct access of table data > > > > Max, thanks for the information. That will be very useful for other > table queries, but not for this one. For my table in questions there > are 18 fields. I think an index with 18 fields would be a little crazy. > > > On 4/23/2010 3:06 AM, Max Vlasov wrote: > > > > > ...As I add more restrictions on the where-clause it > > > tends to slow down. I realize that this is due to my indexes, but > can't > > > add a lot of indexes because it slows down the insert speed which is > > > more important than the query speed. > > > > > > > > > Nathan, maybe you already knew but just in case... > > > > if your select relies on an index and also queries fields not > presented in > > that index, consider appending these extra fields to the index. It > doesn't > > make sense in terms of search speed, (moreover it will increas the db > > size), > > but this will save time since no extra lookup will be taking place. > > > > So if you have table > > CREATE TABLE a, b, c, > > > > and index > > CREATE INDEX ON a, b > > > > and use query similar to > > SELECT a, b, c ... WHERE a= and b = > > > > sqlite will do extra lookup to get c from the table, > > > > but if you change the index to > > > > CREATE INDEX ON a, b, c > > the same query will get all the data from the index itself saving > time and > > the amount of data flow. > > > > I did a quick test and it showed not only a noticable difference in > time, > > but also a significant difference in amount of the data read. > > > > Max, > > maxerist.net > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
