title: Insert a lot of data that depends on the previously inserted data
body: <p>I am trying to store a "navigation" path on the database
  The paths are stored on the logfile as a string, something like <code>"a1 b1 c1 d1"</code> where each one is a "token"
  I want that for each token I store the path to it, as an example I can have</p>
  
  <blockquote>
    <p>a1 -> b1 -> c1</p>
    
    <p>a1 -> b1 -> c2</p>
    
    <p>a1 -> b2 -> c2</p>
  </blockquote>
  
  <p>So, if I ask all the subtokens for a1 I will get [b1 => 2, b2 => 1] on a token => count format.</p>
  
  <p>This way I can get all the subtokens for a given token and the "usage count" for each of those subtokens.</p>
  
  <p>It is possible to have</p>
  
  <blockquote>
    <p>a1 -> b1 -> c1</p>
    
    <p>g1 -> h1 -> b1</p>
  </blockquote>
  
  <p>But for me, those two b1 are not the same, the count should not be the same.</p>
  
  <p>There should not be a LOT of tokens, but there will be a lot of entries on the logfile so I will expect a big count value for those tokens.</p>
  
  <p>I am representing the data like that (sqlite3):
  id; parent_id; token; count</p>
  
  <p>where the parent_id is a FK to the same table.</p>
  
  <p>My issue is. I have around 50k entries on my log and I can have more.
  I am inserting the data on the database using the following procedure</p>
  
  <pre><code>search for a entry that has the parent_id + token (for the first token the parent_id is null)
  EXISTS: Update the count
  DON'T EXISTS: Create a entry
  Save the ID of the updated entry/new entry as a parent_id
  Repeat until there are no more tokens to consume
  </code></pre>
  
  <p>With 50k entries having an average of 4 tokens per entry it gives 200k tokens to process.
  It does not write a lot of data on the database as a lot of those tokens repeats, even if I can have the same token with different parent_id.
  The issue is.... it is too slow.... I cannot perform an insert in chunks as I depends on the id of an existing one or the id of a new one. Worse I also need to update the count.</p>
  
  <p>I was thinking to use some sort of tree to store this data, but there is the problem where it is possible that there are old records that needs to be preserved and this data needs to be counted on top of the existing one.</p>
  
  <p>I can create the tree using the database + update it with the current data, but it feels like an overcomplicated solution for a problem.
  Does anyone have any idea on how to optimize the insertion of this data?</p>
  
  <p>I am using rails (active record) + sqlite 3.</p>