3

I read that delete operation is slow and I was wondering how can I improve this check.

I have a table which is filled by 10-15k rows every day and every startup I need to clean all records older than 6 months but when the database grows, I start to have speed problem. Close to 1 MILION of records when I run this command - EVEN if I have NOTHING to delete - the software hang for minutes....which is not acceptable:

Using cnn as New SqliteConnection(dbConnection)
    cnn.Open()
    dim cmd as New SQLiteCommand(cnn)
    cmd.CommandText = "DELETE FROM tablename WHERE timecolumn < datetime('now', '-6 months')"
    rowsUpdated = cmd.ExecuteNonQuery
End Using

This results in a few minutes of hang even if no records are deleted.

How can I do it quicker, much quicker?

I'm working on .NET compact framework 3.5 for WinCe 6

Thank you

Riccardo Neri
  • 790
  • 1
  • 11
  • 24
  • Should you be using sqlite for a database this large? Is switching a possibility you could consider? – alexis Feb 11 '14 at 16:23
  • Well, the device capabilities are not exiting...so I prefer to don't switch to SQL if possible. I will if I have no chance...is a Milion a large database for SQLITE? Honestly, I didn't think so – Riccardo Neri Feb 11 '14 at 16:26
  • possible duplicate of [How do I improve the performance of SQLite?](http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite) – Alberto Feb 11 '14 at 16:31
  • is your 'timecolumn' indexed? May speed up the case where nothing is deleted. – AnthonyLambert Feb 11 '14 at 16:32
  • No it's not Anthony, I'm going to do it and test again. And reading also the other post, thanks Alberto – Riccardo Neri Feb 11 '14 at 16:34
  • I don't know about SQLLITE but in SQL I usually just delete like 100,000 at a time to keep the transaction log from getting too big. – paparazzo Feb 11 '14 at 19:31

1 Answers1

3

There are lots of things to tweak here SQLite Optimization. I would try to get the whole of your "tablename" into memory by changing the "PRAGMA cache_size". When you delete it has to rebalance the btree substantially. If it can all be in memory while it does it it will be a lot faster.

I would also add an Index on the 'timecolumn' field. Without it the delete is scanning through every record in the table. It will add to the work done when actually deleting the records which match the condition but it should still be a lot faster overall.

If possible move to a database that supports partions (like postgreSQL) and split your data into time periods using the 'timecolumn'. This makes the DB divide your table into lots of sub tables and really speeds up housekeeping and queries that search between "timecolumn"s...

AnthonyLambert
  • 8,768
  • 4
  • 37
  • 72
  • Thanks, I read that indexing a column will slow down update/insert and this is probably a problem for me...Anyway I will try it together with PRAGMA changes. – Riccardo Neri Feb 11 '14 at 17:10
  • if you are only adding 15k rows a day then indexing shouldn't slow it too much.... – AnthonyLambert Oct 21 '20 at 12:19
  • 4
    I know this is old, but Sqlite appears to have real issues with deleting indexed/FK related tables, particularly with a self join. Doing PRAGMA foreign_keys = 0; DELETE FROM [MyTable] WHERE [criteria]; PRAGMA foreign_keys = 1; sped up my delete from 960 seconds to about 1.2 seconds – Ben McIntyre Oct 28 '20 at 11:14