On 21/02/2011, at 12:41 PM, Roger Binns wrote: >> How can I best scroll though the results of an arbitrary select query? > > If you have infinite time and memory then there is no problem.
Memory and processor are limited (typically iPad/iPhone). Disk space would cope with creating temporary tables if necessary. In general, I am trying to cater for a table or view (ie arbitrary select), the results of which would not fit entirely into memory. > You haven't stated your constraints or how arbitrary "arbitrary" really is. By arbitrary, I mean that the user can type any select query that SQLite allows (or have a view in their schema), which might make use of order by, group by, where, union etc. >> 1. Is there any significant overhead on SQLite from my selecting from a view >> representing the original arbitrary select? That is, will SQLite still use >> any indexes etc correctly? Or do I need to dissect/parse the original select >> statement, changing the where statement etc? > > You can work out the answer yourself by using EXPLAIN and EXPLAIN QUERY PLAN > of some representative examples. Yes, but I wondered if there was some overriding logic that SQLite uses that would provide a theoretical/logical rather than experimental guide. >> 2. If the arbitrary select statement already contains an "order by" clause, >> then I obviously need to use the order by column(s) specified there as the >> keyColumn for the scrolling, rather than override the order by. Is there any >> way to get the sort order of an arbitrary select statement? > > Results are returned in the order requested or randomly(*) if not. Given you > can have subqueries with ordering, collations and all sorts of other things, > trying to extract the actual ordering is as difficult as implementing the > SQLite engine itself. > > (*) In practise it is in btree iteration order but that is not something you > should depend on. Yes, I understand that the sort order of results cannot be counted on as consistent if no order by clause is give. But if I am imposing a sort order (eg by rowid) I want to as closely as possible match the undefined sort order so the results look the same. Is sorting by rowid in a table as close I could get to this? What order by sequence could I best use to match the results of a select from joined tables? > You can even "ORDER BY random()". Hmm, good point. I guess in that case (are there other cases?) I can't count on the results being the same from one select to the next, so preparing the statement, extracting some rows, closing, then preparing and extracting again when the user scrolls won't work, since the results will change. If there is a random() component then I would have to leave the query/prepare open, denying all other access to that database file, until there will definitely be no more scrolling. Correct? > The rest of your questions assume a particular solution. The only thing that > will reliably work is to reissue the query using skip and limit By "skip" do you mean select where rowid > <last extracted value>, or offset or something else? > assuming no changes in between. Yes, I can assume no changes in between (though the random() possibility above will make this approach fail I think). > This is if you are trying to save memory/disk and there is no possibility of > changes between scrolling operations. Yes. > If you need to be resilient to that too (implied by "arbitrary" since user > defined functions could have side effects) Hmm, true. I hadn't thought of user defined function side effects. I don't have to allow for that at the moment, but I'll keep it in mind. Is it common or even good practice for a user function (used in a select statement) to modify the table from which it's selecting? That seems like bad practice to me and I can't see why you'd do that rather than use update, insert or delete rather than select to make changes. > then the solution is to 'CREATE TEMP TABLE results AS ...select...'. This > will also work if someone uses > "ORDER BY random()" or any other udf that depends on more than its arguments. Hmm, good thinking. I'll consider that. The downside is that creating a temporary table would require SQLite to process every row in the select, whereas prepare/step only processes the rows as they are shown. This would make a big difference for very large data sets or for a view/select containing a complex calculation for each row. Thanks for taking the time to explore some possibilities for me. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
