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

List:       sqlite-users
Subject:    Re: [sqlite] Attached databases and union view.
From:       Peter da Silva <resuna () gmail ! com>
Date:       2019-08-22 18:40:54
Message-ID: CAMbH6s-dP1Xj6288DxJLb6e0hpcsN8HHG-7BNXj5sBOwxHrVWA () mail ! gmail ! com
[Download RAW message or body]

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 <kmedcalf@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-
> >bounces@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 <resuna@gmail.com>
> >wrote:
> >
> >> Legit. I'll try that.
> >>
> >> On Thu, Aug 22, 2019 at 11:33 AM David Raymond
> ><David.Raymond@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-bounces@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

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