Skip to main content
  • EN

Background

I'm having trouble deleting data from an Sqlite database. My data is strutured in four tables. The database is only about 160 MB in size. I select the data to be deleted with an SQLExecutor and then I send it off to a writer with Writer mode set to DELETE (and the primary key chosen as SQL Key Columns). Since the data contains only this small amount of data there are no indexes on the tables. Foreign keys are set up in SQLite Studio.

Problem: the deletion takes a very long time to finish.

I have tried other ways and also I've tried deleting directly in SQLite Studio, but I can't find a fast way.

Question: which is the preferred and fastest way to delete data in an FME / Sqlite environment? Any suggestions?

Kind regards, Peter

If you're deleting all the records, consider dropping the table and re-creating it instead (it seems SQLite doesn't support truncate). It should be a lot faster.

If you're only deleting parts of the contents, make sure that the columns that figure in your WHERE-clause are indexed, otherwise you will force SQLite to do a full table scan for each and every record that is to be deleted.

If you're deleting all the records, consider dropping the table and re-creating it instead (it seems SQLite doesn't support truncate). It should be a lot faster.

If you're only deleting parts of the contents, make sure that the columns that figure in your WHERE-clause are indexed, otherwise you will force SQLite to do a full table scan for each and every record that is to be deleted.

I'm only deleting parts, but I'll try indexing to see if that speeds up the process. Thanks, David!

If you're deleting all the records, consider dropping the table and re-creating it instead (it seems SQLite doesn't support truncate). It should be a lot faster.

If you're only deleting parts of the contents, make sure that the columns that figure in your WHERE-clause are indexed, otherwise you will force SQLite to do a full table scan for each and every record that is to be deleted.

I'm checking this as we write. BIG difference, David. Thanks, this solved my problem!

Community Stats

31,863
Posts
121,007
Replies
39,490
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.