What is the most efficient way to RESET an SQLite Table

 

This question already has an answer here:

  • Improve INSERT-per-second performance of SQLite? 10 answers

    • This is something of a general answer to a general question, but one of these solutions may be useful for you:

  • EDIT: Using TRUNCATE is not supported; using the truncate optimization does not add anything to the solution that was already tried in the question. If the unconditional DELETE FROM [table] is too slow, try using TRUNCATE TABLE . It may be much faster; it has been so for me in certain scenarios. You'll have to benchmark it for your own situation, though.
  • Alternatively, try dropping and re-creating the table (this can be tricky if multiple threads are talking to the table at the same time; in this case you may want an idempotent table-creation function, or a lock around it).

    • If all of those are too slow, the performance issue may be caused by the amount of time it takes to talk to the persistent storage used by your database. In that case, you have a few other options:

  • Ensure your sqlite database is tuned for performance, if possible. This may include switching it to/from WAL mode, and/or controlling synchronization chunk sizes or other performance tricks.
  • If all else fails, a modification to your code's behavior may be in order. If you can make a new table with a temporary/unique name, eg searchable_coins_489 (and tell code that writes to the table the new name), then you can "fork and forget" the deletion: create a new thread, issue a DROP TABLE in it, and then forget about it (don't wait for it to complete) in your main thread. Since the database is backed by the same files, this may not yield quite the same performance benefits as it would if there were a multiprocess remote database, but it may help your situation some--especially if you're in WAL mode. Again, benchmarking is key here.
    • 链接地址: http://www.djcxy.com/p/19782.html

    上一篇: 为什么“while（！feof（file））”总是错的？

    下一篇: 什么是重置SQLite表的最有效方法