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
