> INSERT OR IGNORE INTO Table (Field) SELECT SomeOtherField FROM OtherTable > ORDER BY SomeOtherField > > produced significant reduce in data flow. (Field Is indexed in Table). I > don't think the difference is related to some caching since the variant > without ORDER BY shows 50 MB data transfer for 17 MB base while adding ORDER > BY reduces it to 1 MB (1:50 ratio so far).
That makes sense and means that when you pre-sort all records in the same order as they should go into index then during insertion index grows without inserting in the middle. It doesn't require all those b-tree node rebuilding and splitting as would unsorted insertion need. > But my other query uses more complex schema (with LEFT JOIN and several > fields (although indexed together)). The problem is I could not optimize the > query to reduce the data flow in this case. > Are there any specific requirements for the inserts like the first one? General requirement: help SQLite to execute query with indexes on those columns that SQLite uses during joins or order-bys. Also if size of all indexes/tables that SQLite uses in your query is bigger than size of cache then increasing cache size will reduce amount of physical reads from disk. If you want more specific advice then you better show us your query. Pavel On Tue, Dec 29, 2009 at 12:00 PM, Max Vlasov <max.vla...@gmail.com> wrote: > The code I use can calculate data flow for sql queries (summing xRead iAmt > in VFS) and I noticed that many variations of INSERT SELECT led to very big > data flow (multiplication of the db size). I thought that such queries can > be optimized if both tables are indexed accordingly and finally the > following query > > INSERT OR IGNORE INTO Table (Field) SELECT SomeOtherField FROM OtherTable > ORDER BY SomeOtherField > > produced significant reduce in data flow. (Field Is indexed in Table). I > don't think the difference is related to some caching since the variant > without ORDER BY shows 50 MB data transfer for 17 MB base while adding ORDER > BY reduces it to 1 MB (1:50 ratio so far). > > But my other query uses more complex schema (with LEFT JOIN and several > fields (although indexed together)). The problem is I could not optimize the > query to reduce the data flow in this case. > Are there any specific requirements for the inserts like the first one? > Couldn not to find information about this in the documentation. > > Thanks > > Max > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
