-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 02/20/2011 04:47 PM, BareFeetWare 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. You haven't stated your constraints or how arbitrary "arbitrary" really is. > 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. > 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. You can even "ORDER BY random()". (*) In practise it is in btree iteration order but that is not something you should depend on. 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 assuming no changes in between. This is if you are trying to save memory/disk and there is no possibility of changes between scrolling operations. If you need to be resilient to that too (implied by "arbitrary" since user defined functions could have side effects) 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. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEQEARECAAYFAk1hwsUACgkQmOOfHg372QTmTgCYrErijaVbARjH772SJC9qID2S hgCYt7OxymRNAUhOjyUBQvDuoluQJw== =GPYv -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
