0

I create the table in SQLite:

CREATE TABLE IF NOT EXISTS abc (abcID INTEGER, primeK TEXT NOT NULL PRIMARY KEY, b INTEGER)

Also I build other tables: 

CREATE TABLE IF NOT EXISTS ref0 (abcID INTEGER)
CREATE TABLE IF NOT EXISTS ref1 (abcID INTEGER)
.
.
.
CREATE TABLE IF NOT EXISTS refn (abcID INTEGER)

Later I want to :

  1. create new table refnn (abcID INTEGER)
  2. insert number of records into abc
  3. for every record -

    3.1 generate new abcID 3.2 if a record with given primeK already exists then I want to retrieve the existing field abcID and put it into refnn; 3.3 Otherwise - put generated abcID to refnn

Table abc is indexed by all of its values

NOTE: table refn may contain multiple unique references to records from abc and each record from abc may be included in refn

I found that when I do not retrieve the existing abcID value then the UPDATE performs fast enough. When I add SELECT statement to retrieve the abcID then the performance deteriorate severely. How can I achieve these goals without significant performance hit?

BorisV
  • 683
  • 3
  • 20
  • Why are you creating ref tables? Just create a column called `ref` in table `abc` and enter relevant data in `ref` column. It might be better to use a different field as primary instead of TEXT field. Have you also thought about auto incrementing abcID (https://www.sqlite.org/autoinc.html)? – zedfoxus Nov 27 '15 at 20:00
  • Forgot to mention: table *refn* may contain multiple unique references to records from *abc* and each record from *abc* may be included in *refn* – BorisV Nov 27 '15 at 20:32
  • In that case, you could create a table with reference and abcID combination. That way both tables are normalized to some extent. – zedfoxus Nov 27 '15 at 20:34
  • I know.But this appears to be terrible slow solution for bulk insertion. – BorisV Nov 27 '15 at 21:07
  • What programming language are you using to do bulk insertion? – zedfoxus Nov 27 '15 at 21:21
  • Have you tried using a transaction when entering data or any of the methods talked about in [this](http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) question? – zedfoxus Nov 27 '15 at 22:15
  • yes, I use transactions, also I configured memory, journal, synch settings to maximum speed. But I have really heavy updates, up to 1M records (which I divide to 100K portions) that may be associated with 1 or more refn tables so every additional query in insertion cycle causes very distinct impact. – BorisV Nov 28 '15 at 07:11

0 Answers0