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

Reply via email to