> It would/should have told you immediately that you needed those two
> additional indexes, I should think.
Unfortunately not. Someone told me about ".expert" some time ago and it's
indeed helpful for me because I never know what indexes to create and why. But
for this database everything
On Friday, 1 November, 2019 14:52, Thomas Kurz wrote:
>That's it!!! You're a genius! Thank you very very much!
>Run Time: real 8.290 user 3.25 sys 1.906250
I believe that the SQLite3 CLI has a command .expert that you can use to turn
on assistance to tell you if the next executed SQL
On 1 Nov 2019, at 10:11pm, Keith Medcalf wrote:
> Just run ".schema" or ".schema --indent"
But there's no need, since I think the problem has been solved, right ?
It came down to lack of indexes. Which is consistent with the times and
EXPLAIN QUERY PLANs posted.
On Friday, 1 November, 2019 16:04, Thomas Kurtz wrote:
> The database schema is not a secret. If it helps, I can post it, that's
> no problem. Is it enough to run ".dump" on a database without data?
Just run ".schema" or ".schema --indent" which will output only the schema
definitions and not
The database schema is not a secret. If it helps, I can post it, that's no
problem. Is it enough to run ".dump" on a database without data?
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Friday, November 1, 2019, 22:57:02
Subject: [sqlite] DELETE extremely slow
On Friday, 1 November, 2019 15:12, Simon Slavin wrote:
>So the slow-down in the DELETE FROM command is caused by a TRIGGER, but
>there are no TRIGGERs on DELETE ? I don't understand that. Can someone
>explain, please ?
The code indicates that they are AFTER DELETE so presumably they are
So the slow-down in the DELETE FROM command is caused by a TRIGGER, but there
are no TRIGGERs on DELETE ? I don't understand that. Can someone explain,
please ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
On Thursday, 31 October, 2019 16:54, Thomas Kurz :
>I did it again, same file:
>SQLite version 3.30.1 2019-10-10 20:19:45
>Enter ".help" for usage hints.
>sqlite> pragma foreign_keys=on;
>sqlite> .timer on
>sqlite> delete from dataset;
>Run Time: real 5249.891 user 2412.812500 sys 2606.531250
ave?
-Original Message-
From: sqlite-users On Behalf Of
Thomas Kurz
Sent: Thursday, October 31, 2019 6:54 PM
To: SQLite mailing list
Subject: Re: [sqlite] DELETE extremely slow
> Do you have memory to run this in? Have you increased the sqlite cache size
> because that looks (
That's it!!! You're a genius! Thank you very very much!
Run Time: real 8.290 user 3.25 sys 1.906250
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Friday, November 1, 2019, 18:07:51
Subject: [sqlite] DELETE extremely slow
One of your triggers requires and
One of your triggers requires and index on item(nameid) and there is no index
on item(nameid).
Hence it is doing a table scan to find the rows matching this trigger. That is
why the plan has multiple "SCAN item" in it.
--
The fact that there's a Highway to Hell but only a Stairway to
l but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Thomas Kurz
>Sent: Thursday, 31 October, 2019 16:54
>To: SQLite mailing list
>Subject: Re: [sqlite] DELETE extremely slow
>
>> Do
: [sqlite] DELETE extremely slow
> Do you have memory to run this in? Have you increased the sqlite cache size
> because that looks (to me) an awful lot like I/O thrashing ...
Sorry to disappoint you, Keith and Simon, but in all cases the database file
has been located on a ramdisk. It's only ab
> Do you have memory to run this in? Have you increased the sqlite cache size
> because that looks (to me) an awful lot like I/O thrashing ...
Sorry to disappoint you, Keith and Simon, but in all cases the database file
has been located on a ramdisk. It's only about 50 MB in size, btw.
>
> According to the MariaDB reference manual, it does not "do anything" with
> references clauses on columns.
Thanks for that hint, I will try again tomorrow because I cannot say for sure
now whether it worked correctly or not. (And I don't have that data available
anymore.)
On Thursday, 31 October, 2019 03:51, Thomas Kurz wrote:
>I experimentally imported the same data into a MariaDB database and tried
>the same operation there (without paying attention to creating any
>indexes, etc.). It takes only a few seconds there.
According to the MariaDB reference manual,
On Thursday, 31 October, 2019 10:52, Simon Slavin wrote:
>On 31 Oct 2019, at 4:29pm, Keith Medcalf wrote:
>> If the elapsed time is much greater than the sum of user+sys time then
>> I would suspect it is still I/O thrashing (or the machine is really busy
>> doing something else -- those
On 31 Oct 2019, at 4:29pm, Keith Medcalf wrote:
> If the elapsed time is much greater than the sum of user+sys time then I
> would suspect it is still I/O thrashing (or the machine is really busy doing
> something else -- those original timings represent only a 57% process
> dispatch rate,
On Thursday, 31 October, 2019 10:01, Dominique Devienne
wrote:
>On Thu, Oct 31, 2019 at 4:10 PM Thomas Kurz wrote:
>> Yes, but I'd expect that MariaDB has to do the same, but takes clearly
>> less than 1 minute instead of 88 minutes... :confused:
>Are we comparing apples to oranges here?
On Thu, Oct 31, 2019 at 4:10 PM Thomas Kurz wrote:
> Yes, but I'd expect that MariaDB has to do the same, but takes clearly
> less than 1 minute instead of 88 minutes... :confused:
>
Are we comparing apples to oranges here?
SQLite runs on the local machine. While MariaDB is client-server, so
>pragma foreign_keys=on;
>pragma journal_mode=wal;
>.timer on
>delete from dataset;
>--> Run Time: real 197993.218 user 53015.593750 sys 54056.546875
Wow. That is 14 hours each of System and User time and then and additional 24
hours of "waiting for something to happen" time. Do you have
On 31 Oct 2019, at 3:09pm, Thomas Kurz wrote:
> The result of "DELETE FROM dataset" is now 88 minutes
That still seems wrong. I hope the list can help you track down what's going
on.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
October 31, 2019 11:10 AM
To: SQLite mailing list
Subject: Re: [sqlite] DELETE extremely slow
> Something is wrong. If you did multiple commands like
>
> DELETE FROM MyTable;
>
> to your child tables, they should be fast. Have you run an integrity check ?
I created a new da
> Something is wrong. If you did multiple commands like
>
> DELETE FROM MyTable;
>
> to your child tables, they should be fast. Have you run an integrity check ?
I created a new database now, added the missing index "trace(datasetid)" as
suggested by Keith.
The result of "DELETE FROM dataset"
"Four observations that may (or may not) explain the problem:
- dataset.id declared "unique": useless since the primary key is unique
by definition but it may create an index (not checked). A PK is not
"more unique" if an additional "unique" constraint is declared."
Declaring it as both
t
>Subject: Re: [sqlite] DELETE extremely slow
>
>> Keith found the answer: you don't have the indexes required to make
>your FOREIGN KEYs run quickly.
>
>Thanks, I will try that.
>
>> If you DELETE FROM the child tables first, do you get fast or slow
>times ?
On Oct 31, 2019, at 3:51 AM, Thomas Kurz wrote:
>
> delete from dataset;
Is that command representative of actual use, or are you deleting all rows just
for the purpose of benchmarking?
I ask because if you’re going to just delete all rows in a table, it’s usually
faster to DROP TABLE and
On 31 Oct 2019, at 11:09am, Thomas Kurz wrote:
> Yes, I already tried deleting from each table individually. It's slow
> everywhere.
Something is wrong. If you did multiple commands like
DELETE FROM MyTable;
to your child tables, they should be fast. Have you run an integrity check ?
> Keith found the answer: you don't have the indexes required to make your
> FOREIGN KEYs run quickly.
Thanks, I will try that.
> If you DELETE FROM the child tables first, do you get fast or slow times ?
Yes, I already tried deleting from each table individually. It's slow
everywhere.
>
Four observations that may (or may not) explain the problem:
- dataset.id declared "unique": useless since the primary key is unique
by definition but it may create an index (not checked). A PK is not
"more unique" if an additional "unique" constraint is declared..
- no "on delete <...>" on
On 31 Oct 2019, at 9:51am, Thomas Kurz wrote:
> I'm using a database with 5 hierarchically strcutured tables using foreign
> keys. The largest table contains about 230'000 entries. My problem is that
> deleting in this database is extremely slow:
Keith found the answer: you don't have the
a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Thomas Kurz
>Sent: Thursday, 31 October, 2019 04:25
>To: SQLite mailing list
>Subject: Re: [sqlite] DELETE extremely slow
>
&
Yes, please apologize, I indeed forgot to attach the table definitions:
CREATE TABLE dataset (
id INTEGER PRIMARY KEY AUTOINCREMENT
UNIQUE
NOT NULL,
name STRING DEFAULT NULL
COLLATE NOCASE,
On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz wrote:
> I'm using a database with 5 hierarchically strcutured tables using foreign
> keys. The largest table contains about 230'000 entries. My problem is that
> deleting in this database is extremely slow:
>
> pragma foreign_keys=on;
> pragma
34 matches
Mail list logo
