>> I'm confused. Can someone give me a hint, why reusing the prepared query >> is so much slower? > > What are your indexes exactly? I do have two different indices. One on each field.
> 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. I'll try to do that and see if the results will be more equal. Thanks for your support. Thomas > > 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
