conditional trigger

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

conditional trigger

InAsset.Michele Petrazzo
Hi all,
I'm trying to create a conditional trigger for add some data to my
sqlite db only when it isn't inside (yet).

My tables:
CREATE TABLE dir_paths (
     id integer PRIMARY KEY,
     dir_path TEXT NOT NULL UNIQUE
);
CREATE TABLE files (
     id INTEGER PRIMARY KEY,
     f_name TEXT NOT NULL,
     dir_id integer NOT NULL,
     FOREIGN KEY (dir_id) REFERENCES dir_paths (id)
);

My need: I would like to create a trigger (or a view + a trigger
associate) that check if dir_path (and of course dir_paths.id) is
present and if not, create it into a single INSERT transition.
If yes, SELECT the dir_path.id and pass it to the INSERT into files
statement.
On the other hand, I have already a function that select the id of
dir_paths if present, and if not insert into it. But for performances
reasons, I need a single transition.

I tried with view+trigger:
CREATE VIEW select_file_dirs AS
       SELECT   dir_paths.dir_path AS dir_path,
                files.f_name,
       FROM dir_paths
       INNER JOIN
        files ON files.dir_id = dir_path.id;
+
CREATE TRIGGER check_dir_present INSTEAD OF INSERT ON select_file_dirs
BEGIN
CASE WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL)
THEN
 INSERT INTO dir_paths (directory) VALUES (NEW.dir_path)
END

but I receive:
Error: near "CASE": syntax error

on the other side, I create a trigger before the insert into files, but
In the "INSERT INTO files" I haven't the "dir_path" info...

CREATE TRIGGER check_dir_present BEFORE INSERT ON files
  WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL)
BEGIN
INSERT INTO dir_paths (dir_path) VALUES (NEW.dir_path);
END;

Some may help me?

Thanks
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: conditional trigger

David Raymond
Your create view had 2 typos in it by the way. Here's a fixed version for those copying and pasting:

CREATE VIEW select_file_dirs AS
       SELECT   dir_paths.dir_path AS dir_path,
                files.f_name
       FROM dir_paths
       INNER JOIN
        files ON files.dir_id = dir_paths.id;


I think you're also going to want a unique constraint on files (dir_id, f_name) to avoid duplicate file names

What you're looking for is this I believe:


create trigger trg_select_file_dirs_insert
instead of insert on select_file_dirs
begin
  insert into dir_paths (dir_path) values (new.dir_path) on conflict (dir_path) do nothing;
  insert into files (f_name, dir_id) values (new.f_name, (select id from dir_paths where dir_path = new.dir_path));
end;




-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of InAsset.Michele Petrazzo
Sent: Thursday, August 22, 2019 6:30 AM
To: [hidden email]
Subject: [sqlite] conditional trigger

Hi all,
I'm trying to create a conditional trigger for add some data to my
sqlite db only when it isn't inside (yet).

My tables:
CREATE TABLE dir_paths (
     id integer PRIMARY KEY,
     dir_path TEXT NOT NULL UNIQUE
);
CREATE TABLE files (
     id INTEGER PRIMARY KEY,
     f_name TEXT NOT NULL,
     dir_id integer NOT NULL,
     FOREIGN KEY (dir_id) REFERENCES dir_paths (id)
);

My need: I would like to create a trigger (or a view + a trigger
associate) that check if dir_path (and of course dir_paths.id) is
present and if not, create it into a single INSERT transition.
If yes, SELECT the dir_path.id and pass it to the INSERT into files
statement.
On the other hand, I have already a function that select the id of
dir_paths if present, and if not insert into it. But for performances
reasons, I need a single transition.

I tried with view+trigger:
CREATE VIEW select_file_dirs AS
       SELECT   dir_paths.dir_path AS dir_path,
                files.f_name,
       FROM dir_paths
       INNER JOIN
        files ON files.dir_id = dir_path.id;
+
CREATE TRIGGER check_dir_present INSTEAD OF INSERT ON select_file_dirs
BEGIN
CASE WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL)
THEN
 INSERT INTO dir_paths (directory) VALUES (NEW.dir_path)
END

but I receive:
Error: near "CASE": syntax error

on the other side, I create a trigger before the insert into files, but
In the "INSERT INTO files" I haven't the "dir_path" info...

CREATE TRIGGER check_dir_present BEFORE INSERT ON files
  WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL)
BEGIN
INSERT INTO dir_paths (dir_path) VALUES (NEW.dir_path);
END;

Some may help me?

Thanks
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Attached databases and union view.

Peter da Silva-2
In reply to this post by InAsset.Michele Petrazzo
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

Warren Young
On Aug 22, 2019, at 9:27 AM, Peter da Silva <[hidden email]> wrote:
>
> Have an existing application that's pushing the limit

If the limit is in hardware, shards won’t help.

For example, a SQLite DB on a 7200 RPM spinning disk is limited to about 60 transactions per second under the stock SQLite fsync logic, since each takes 2 revolutions to commit.  (One to write to the journal, and one to commit the journal entry.)  Writes to multiple shards only get to share a platter rotation if there is no seek between writes.

The limits are higher for SSDs, but there’s still a low limit on the number of parallel writes.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

Peter da Silva-2
Database is on tmpfs and periodically snapshotted to SSD. There are
bottlenecks upstream of sqlite that we can see in traces.

On Thu, Aug 22, 2019 at 10:36 AM Warren Young <[hidden email]> wrote:

> On Aug 22, 2019, at 9:27 AM, Peter da Silva <[hidden email]> wrote:
> >
> > Have an existing application that's pushing the limit
>
> If the limit is in hardware, shards won’t help.
>
> For example, a SQLite DB on a 7200 RPM spinning disk is limited to about
> 60 transactions per second under the stock SQLite fsync logic, since each
> takes 2 revolutions to commit.  (One to write to the journal, and one to
> commit the journal entry.)  Writes to multiple shards only get to share a
> platter rotation if there is no seek between writes.
>
> The limits are higher for SSDs, but there’s still a low limit on the
> number of parallel writes.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

test user
What language/binding library are you using?

On Thu, 22 Aug 2019 at 16:45, Peter da Silva <[hidden email]> wrote:

> Database is on tmpfs and periodically snapshotted to SSD. There are
> bottlenecks upstream of sqlite that we can see in traces.
>
> On Thu, Aug 22, 2019 at 10:36 AM Warren Young <[hidden email]> wrote:
>
> > On Aug 22, 2019, at 9:27 AM, Peter da Silva <[hidden email]> wrote:
> > >
> > > Have an existing application that's pushing the limit
> >
> > If the limit is in hardware, shards won’t help.
> >
> > For example, a SQLite DB on a 7200 RPM spinning disk is limited to about
> > 60 transactions per second under the stock SQLite fsync logic, since each
> > takes 2 revolutions to commit.  (One to write to the journal, and one to
> > commit the journal entry.)  Writes to multiple shards only get to share a
> > platter rotation if there is no seek between writes.
> >
> > The limits are higher for SSDs, but there’s still a low limit on the
> > number of parallel writes.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

David Raymond
In reply to this post by Peter da Silva-2
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 <[hidden email]> On Behalf Of Peter da Silva
Sent: Thursday, August 22, 2019 11:28 AM
To: SQLite mailing list <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

Peter da Silva-2
Legit. I'll try that.

On Thu, Aug 22, 2019 at 11:33 AM David Raymond <[hidden email]>
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 <[hidden email]> On
> Behalf Of Peter da Silva
> Sent: Thursday, August 22, 2019 11:28 AM
> To: SQLite mailing list <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

Peter da Silva-2
In reply to this post by test user
Originally Tcl/native Tcl binding, now a C++ extension calling the
C-binding that's a Tcl extension itself.

On Thu, Aug 22, 2019 at 11:17 AM test user <[hidden email]>
wrote:

> What language/binding library are you using?
>
> On Thu, 22 Aug 2019 at 16:45, Peter da Silva <[hidden email]> wrote:
>
> > Database is on tmpfs and periodically snapshotted to SSD. There are
> > bottlenecks upstream of sqlite that we can see in traces.
> >
> > On Thu, Aug 22, 2019 at 10:36 AM Warren Young <[hidden email]>
> wrote:
> >
> > > On Aug 22, 2019, at 9:27 AM, Peter da Silva <[hidden email]> wrote:
> > > >
> > > > Have an existing application that's pushing the limit
> > >
> > > If the limit is in hardware, shards won’t help.
> > >
> > > For example, a SQLite DB on a 7200 RPM spinning disk is limited to
> about
> > > 60 transactions per second under the stock SQLite fsync logic, since
> each
> > > takes 2 revolutions to commit.  (One to write to the journal, and one
> to
> > > commit the journal entry.)  Writes to multiple shards only get to
> share a
> > > platter rotation if there is no seek between writes.
> > >
> > > The limits are higher for SSDs, but there’s still a low limit on the
> > > number of parallel writes.
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

Peter da Silva-2
In reply to this post by Peter da Silva-2
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 <[hidden email]> wrote:

> Legit. I'll try that.
>
> On Thu, Aug 22, 2019 at 11:33 AM David Raymond <[hidden email]>
> 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 <[hidden email]> On
>> Behalf Of Peter da Silva
>> Sent: Thursday, August 22, 2019 11:28 AM
>> To: SQLite mailing list <[hidden email]>
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

Keith Medcalf

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-
>[hidden email]] 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 <[hidden email]>
>wrote:
>
>> Legit. I'll try that.
>>
>> On Thu, Aug 22, 2019 at 11:33 AM David Raymond
><[hidden email]>
>> 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 <[hidden email]>
>On
>>> Behalf Of Peter da Silva
>>> Sent: Thursday, August 22, 2019 11:28 AM
>>> To: SQLite mailing list <[hidden email]>
>>> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attached databases and union view.

Peter da Silva-2
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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]>
> >wrote:
> >
> >> Legit. I'll try that.
> >>
> >> On Thu, Aug 22, 2019 at 11:33 AM David Raymond
> ><[hidden email]>
> >> 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 <[hidden email]>
> >On
> >>> Behalf Of Peter da Silva
> >>> Sent: Thursday, August 22, 2019 11:28 AM
> >>> To: SQLite mailing list <[hidden email]>
> >>> 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
> >>> [hidden email]
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>> _______________________________________________
> >>> sqlite-users mailing list
> >>> [hidden email]
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>>
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Attached databases and union view.

Hick Gunter
In reply to this post by Peter da Silva-2
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:[hidden email]] Im Auftrag von Peter da Silva
Gesendet: Donnerstag, 22. August 2019 17:28
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users