> Message: 9 > Date: Sun, 23 Aug 2009 21:33:06 -0500 > From: "Jay A. Kreibich" <j...@kreibi.ch> > Subject: Re: [sqlite] can sqlite result be sorted by using > "prepare-step" API > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: <20090824023306.gb1...@uiuc.edu> > Content-Type: text/plain; charset=us-ascii
hi Jay, > > 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. > Thanks. > > 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? Sorry I am a bit lazy and busy right now and that is why I choose to post this question instead of doing the experiment myself. > > > 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. Your explanation is really convincing and helpful! If SQLite can return the sorted result without waiting to retrieve all dataset, then it would be a great help as I am currently involved in porting sqlite on mobile phone where time-consumed query would probably reset the phone. And that's why I am interested to see if the first awailable row can return ASAP. And if "prepare-step" can not do better than "execute" or "GetTable", then what is meaning to use "prepare-step"? And here comes my little question: Suppose my query needs to be sorted by some field and if I create a view with clause of "order by" to ask sqlite to sort on that index. Can I get sorted result by querying the view with prepare-step? In documents, sqlite doesn't say "order by" is not allowed in create view. I know most of database like MS sqlserver doesn't allow "order by" clause in create view statement. However, sqlite is the most unique database which gives me a lot of surprise and I hope it can do it again. > > > 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. > According to your explanation, it seems the sorting prevents "prepare-step" from returning faster than "execute". If this is correct understanding, the implication is that without using "order by" clause the prepare-step API would return much quicker than "execute" because it doesn't have to wait for all result set being available. And this would be especially useful for embedded system. Thanks again, nick > -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 > > _________________________________________________________________ More storage. Better anti-spam and antivirus protection. Hotmail makes it simple. http://go.microsoft.com/?linkid=9671357 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
