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
