> I'm confused. Can someone give me a hint, why reusing the prepared query > is so much slower?
What are your indexes exactly? If you have 2 different indexes for Column3 and Column4 then SQLite has to choose which index to use during preparing. According to your results in first general case it chooses some of the indexes randomly and for your particular values this index doesn't selective enough to speed up the query. In the second case optimizer can see values and thus can choose the index that will be more selective based on those values... If you create 1 index on both fields then I think your results will be the same. Pavel On Fri, Mar 19, 2010 at 5:39 AM, Thomas Kopp <thom...@web.de> wrote: > I'm using SQLite3 on an embedded system for logging some kind of > information. > > I have 4 SQL-statements (2x SELECT, 1x INSERT, 1x DELETE) which are > called every time I information has to be logged (which can be several > times within a second or only once in an hour). I decided to wrap these > 4 statements in a beginn/commit - transaction. For saving speed, I > prepare those 4 statements only once (sqlite_prepare_v2()) and whenever > I have to execute the queries I only bind parameters and step through > the results (see: chapter 3, http://www.sqlite.org/cintro.html). At the > end of every cycle, I call sqlite_reset() and sqlite_clear_bindings(). > > Using this method I would expect an increase in speed setting up, and > executing the queries, but instead I noticed that reusing some queries > slows the execution (sqlite_step()) down. > > An example: > Query: SELECT Column1, Column2 FROM table WHERE Column3=? AND Column4=? > For faster access the fields "Column3" and "Column4" are indexed. > > When I'm using the prepared query and just bind the parameters the > query-execution (sqlite_step()) takes more than a second. When I prepare > the query every time it has to be executed, I'm able to compose the > whole query, so that it looks (for example) like that: > SELECT Column1, Column2 FROM table WHERE Column3=23 AND Column4=999852 > In this case the query - execution only takes some 100us. > > I'm confused. Can someone give me a hint, why reusing the prepared query > is so much slower? > > Thanks in advance, > Thomas > _______________________________________________ > 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
