Hi, I do not have a ORDER BY , only a WHERE clause. So sorting should not be the cause for the overhead.
-Sreekumar On Mon, Oct 17, 2011 at 9:08 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 17 Oct 2011, at 4:22am, Sreekumar TP wrote: > > > In case of a prepared statement that uses SELECT > > , the first sqlite3_step statement consumes a lot of time (order of > > magnitude can be 100 or more) > > when compared to the subsequent executions of step used to iterate > through > > the results. Does the first execution of step cache the entire result set > > and tje subsequent steps get the results from the cache? > > The first step has to make a lot of decisions about what plan to follow in > retrieving the rows: Which index is the best to use ? Is it going to be > necessary to sort the rows even after that index ? These things do take > some extra time. > > If it turns out that one of the available indexes will produce the rows in > exactly the order wanted, then it will not be necessary to store all the > rows which should be returned. SQLite just stores the query plan: it > remembers how to find each next row, rather than remembering the data in > those rows. > > On the other hand, if it is necessary for SQLite to sort the rows itself, > because no good index has been created, then yes, it will have to sort the > rows in order to figure out which one to return first, and having sorted > them it will store them so that subsequent _step()s can just return the next > row. > > Simon. > _______________________________________________ > 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