On Mon, Aug 24, 2009 at 01:55:41AM +0000, nick huang scratched on the wall: > > I am a kind of new to Sqlite and just wonder if the query result row > could be sorted by using Sqlite_prepare followed by Sqlite_Step.
Prepare/step is the only way to get data out of the database, so yes. > For example, query statement is something like "select * from > sometable order by somefield;" and we call prepare followed by step. > And are all results returned from "step" in order of "somefield"? Yes. Did you try it and see? > As I read the document, it seems the step will return the first awailable > row ASAP. That is why I wonder the sorting is not possible as according > to what we learned from books the sorting of dataset is done at the > last stage of SQL query when all result set is available. It depends. If SQLite is sorting based off an indexed column, it may be able to start returning rows right away before the full result set has been computed. There are plenty of cases when the query optimizer can rearrange the query pipeline to produce "presorted" in-order results without the whole result set at hand. On the other hand, if you're sorting on a non-indexed column or computed result-set column, then the database engine has to compute the entire result, sort it, and then start to return it. In the first case, the cost of doing the query will be spread across each call to sqlite3_step(). In the second case, the first call to sqlite3_step() may be quite long, but all calls after that should be quite fast. > However, this also seems to contradictive to that all other query API > like "exec", "getTable" etc. which all support "sorting" are all > based on prepare-step. Therefore the only conclusion is that "exec", > "getTable" etc. retrieve dataset and sort by themselves after they > call "prepare-step". No, the short-cut functions are not that smart. The much simpler and more logical conclusion is that the database engine does the sorting. sqlite3_step() returns rows as they become available. You're reading too deeply into "as they become available", however. The database engine is still required to return the correct result set. If the query needs to be sorted, it needs to be sorted. That may or may not require computing the full result set before returning the first row. It depends on the query. But either way, the database will do the right thing and return the correct results. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
