Looks interesting, but if I'm reading the descriptions right I don't think those would help: my partitioning can be on a hash of a unique text ID or on a geographic area, and both of those seem to be based on unique rowid ranges.
On Thu, Aug 22, 2019 at 1:23 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > Have you looked at the swarmvtab or unionvtab extension? > > https://www.sqlite.org/unionvtab.html > https://www.sqlite.org/swarmvtab.html > > Which can "avoid" having to write your own unions. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-----Original Message----- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Peter da Silva > >Sent: Thursday, 22 August, 2019 11:57 > >To: SQLite mailing list > >Subject: Re: [sqlite] Attached databases and union view. > > > >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 > > > > _______________________________________________ > 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