Slow writing on SQLite database after ALTER

Question

I have a relatively big SQLite database (~900 MB) with 3 tables inside. I recently removed a field from the main table, through the software SQLlitebrowser. I used the answer found here: Delete column from SQLite table

And I basically ran this:

BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(id, percentage_match, doi, title, date, journal, authors, abstract, graphical_abstract, liked, url, new, topic_simple, author_simple); INSERT INTO t1_backup SELECT id, percentage_match, doi, title, date, journal, authors, abstract, graphical_abstract, liked, url, new, topic_simple, author_simple FROM papers; DROP TABLE papers; CREATE TABLE papers(id, percentage_match, doi, title, date, journal, authors, abstract, graphical_abstract, liked, url, new, topic_simple, author_simple); INSERT INTO papers SELECT id, percentage_match, doi, title, date, journal, authors, abstract, graphical_abstract, liked, url, new, topic_simple, author_simple FROM t1_backup; DROP TABLE t1_backup; COMMIT;

I also tried to run that query through a python script.

After the alteration of the database, writing into the main table is extremely slow. When I try to write on the original database (I made a backup before removing the field), I can write into the main table more or less 10000 times faster.

Do you have an idea about why this is happening ? I tried to vacuum the newly altered database, but the loss of performance is still here.

EDIT:

@neuhaus: I ran .schema (first time I hear about it, forgive me if I misused it) on the new table:

sqlite> .schema papers CREATE TABLE papers(id, percentage_match, doi, title, date, journal, authors, abstract, graphical_abstract, liked, url, new, topic_simple, author_simple);

And on the old table:

sqlite> .schema papers CREATE TABLE IF NOT EXISTS "papers" ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `percentage_match` REAL, `doi` TEXT, `title` TEXT, `date` TEXT, `journal` TEXT, `authors` TEXT, `abstract` TEXT, `graphical_abstract` TEXT, `liked` INTEGER, `url` TEXT, `new` INTEGER, `topic_simple` TEXT, `author_simple` TEXT , url_image TEXT);

It's obvious there is a difference. It seems the types of the fields are not present in the new table. What do you think, and how do I correct that ? ?

neuhaus · Accepted Answer · 2017-10-11T13:30:42.727

0

Compare the schema of the old table and the new table using the sqlite3 utility and its .schema command.

The new table is likely to be missing an index.

Use the CREATE TABLE statement given to you by the output of the .schema command (without the column you want to drop) instead of the one you are using now.

edited Oct 11 '17 at 13:30

answered Oct 11 '17 at 13:23

neuhaus

3,886
1
10
27

I edited my question to add the results of ```schema``` – JPFrancoia Oct 11 '17 at 13:29
I edited my answer. Just change the create table statement and add the primary key. You probably also want to add the AUTOINCREMENT that was in the old table. – neuhaus Oct 11 '17 at 13:31
Should I also add the type for the other fields ? – JPFrancoia Oct 11 '17 at 13:34
1

yes, unless you want to change the type for some reason (which I consider unlikely) – neuhaus Oct 11 '17 at 13:35

Slow writing on SQLite database after ALTER

1 Answers1