-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 >>> 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.
There isn't as trying EXPLAIN would show you. >> 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? No, I mean skipping the first N results. You can't use rowid since it won't exist in many cases. For example 'select 3+4' or 'select x+y from a,b where ...'. > 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. The UDF could take a filename as a parameter and return the size or last access time. By changing when bits of the query execute you'll get different answers (eg the file size changes between page scrolls in the query). >> 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. I think you are overthinking the problem. No one is going to scroll through 100,000 results so there is no need to save 100,000 of them. Pick an arbitrary number (eg 1000), use the CREATE TEMP TABLE ... AS .. select ... approach, and add a 'LIMIT 1000' on the end. This will work with any query and work reliably no matter what else happens to the database (eg other processes modifying it). If you want to refine things then there are several callbacks you can use. For example if the database is coming back with one row per second then you don't really want to wait 1000 seconds. You can have a monitoring thread and call sqlite3_interrupt to abort the query. If you don't want to use another thread then you can register a progress callback which knows when the query started. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1jMKcACgkQmOOfHg372QTfbQCgoO3rzpBFmcZIZf2FKJitXaWv t7AAniZ//1kazi0NIXFeUoGCqTkUwKs3 =ISAF -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
