Still a bit over 3x slower on queries but that's a 7x performance improvement.
On Thu, Aug 22, 2019 at 11:40 AM Peter da Silva <res...@gmail.com> wrote: > Legit. I'll try that. > > On Thu, Aug 22, 2019 at 11:33 AM David Raymond <david.raym...@tomtom.com> > wrote: > >> I don't know how smart the planner is, but as a thought, would UNION ALL >> make any improvement over just UNION? With just UNION it has to >> de-duplicate all the subquery results whereas with UNION ALL it would be >> free to separate all the various subqueries from each other. >> >> Or do you actually need the UNION to de-dupe stuff? >> >> >> -----Original Message----- >> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >> Behalf Of Peter da Silva >> Sent: Thursday, August 22, 2019 11:28 AM >> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >> Subject: [sqlite] Attached databases and union view. >> >> Have an existing application that's pushing the limit on how fast it can >> read data and add it to the database, and thinking of sharding the >> database >> file so I can have multiple writers writing to shards of the main tables. >> >> ATTACH DATABASE 'shard0.sqlite' as shard0; >> ATTACH DATABASE 'shard1.sqlite' as shard1; >> ... >> >> CREATE TEMPORARY VIEW sharded_main_table AS >> SELECT col,col,col...,all_columns_basically FROM shard0.main_table >> UNION >> SELECT col,col,col...,all_columns_basically FROM shard1.main_table >> ...; >> >> What's the best way to construct this union view so the query optimizer >> won't be horribly confused? If I run something like "SELECT count(*) FROM >> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower >> than the same query against the original main_table. Running the query >> against each shardN.main_table it's actually faster (in total time for all >> queries in sequence) than running it against the original table. >> >> Is there a better way to construct the view, or am I going to get best >> query performance by making my code shard-aware? >> >> All the original indexes on main_table have been copied to the shard >> databases. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
