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
