I am using the Begin and Commit.
On 4/23/2010 9:04 AM, Black, Michael (IS) wrote: > > 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