Skip to main content
Question

How do I speed up delete in FME/SQLite database environment

peteralstorp
Contributor
Forum|alt.badge.img

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

3 replies

david_r
Celebrity
  • david_rCelebrity
  • July 11, 2016

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.

peteralstorp
Contributor
Forum|alt.badge.img
  • peteralstorpContributor
  • Author
  • Contributor
  • July 12, 2016
david_r wrote:

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!

peteralstorp
Contributor
Forum|alt.badge.img
  • peteralstorpContributor
  • Author
  • Contributor
  • July 12, 2016
david_r wrote:

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!

Reply


Helpful Members This Week

Community Stats

31,137
Posts
118,051
Replies
38,778
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.