6

I have an application that inserts (or replaces) records with a unique key into a SQLite database table. It is expected to do about 1500 records per second but it's slower than this due to performance issues.

Currently I'm doing it as such (in Python):

for item in items:
    CONN.execute("INSERT OR REPLACE INTO ITEMS (ITEM_ID) VALUES ("+item['itemId']+")")

What I would like is a bulk insert or replace to improve performance.

I know there is a way to do bulk inserts: Is it possible to insert multiple rows at a time in an SQLite database?

But is there a way to do bulk insert or replace? I know that insert or replace is NOT the same as upsert - that's OK.

vy32
  • 28,461
  • 37
  • 122
  • 246
etayluz
  • 15,920
  • 23
  • 106
  • 151

1 Answers1

-1

Try something like this

INSERT OR REPLACE INTO items (id, link, added) VALUES (19122727, "https://instant.page/", COALESCE((SELECT added FROM items WHERE id = 19122727), 1549742777)),(19123506, "http://hades.mech.northwestern.edu/index.php/Modern_Robotics", COALESCE((SELECT added FROM items WHERE id = 19123506), 1549742777)),(19123352, "https://dendibakh.github.io/blog/2019/02/09/Top-Down-performance-analysis-methodology", COALESCE((SELECT added FROM items WHERE id = 19123352), 1549742777))

guy_fawkes
  • 947
  • 8
  • 31