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
