4

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
  • 26,286
  • 33
  • 110
  • 218
etayluz
  • 14,907
  • 20
  • 95
  • 141

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
  • 856
  • 8
  • 29