[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] [EXTERNAL]  Attached databases and union view.
From:       Hick Gunter <hick () scigames ! at>
Date:       2019-08-26 6:37:58
Message-ID: 0e52c64f1ac1771faf04313428e496e7f44441ac () localhost
[Download RAW message or body]

I think you are looking for UNION ALL to avoid creating an ephemeral table to \
implement the implied DISTINCT

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-bounces@mailinglists.sqlite.org] Im Auftrag \
                von Peter da Silva
Gesendet: Donnerstag, 22. August 2019 17:28
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse \
2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[prev in list] [next in list] [prev in thread] [next in thread]