Combining databases: identifying common records -- most efficient way
<p>I have a set of 20 sqlite databases (50 tables each and roughly 100 thousand records total per DB).
I want to combine these 20 databases into one master DB.
The concept is to have an additional column, which indicates for which domain the record is applicable.</p>

<hr>

<p>For example:</p>

<p><strong>Table A</strong></p>

<pre><code>FRUIT | COLOR  | SHAPE
----------------------
apple | red    | round 
banana| yellow | curved
</code></pre>

<p><strong>Table B</strong></p>

<pre><code>FRUIT | COLOR  | SHAPE
----------------------
apple | red    | round 
banana| yellow | curved
</code></pre>

<p><strong>Table C</strong></p>

<pre><code>FRUIT | COLOR  | SHAPE
----------------------
apple | red    | round 
banana| blue   | straight
</code></pre>

<p>These tables (A, B and C) would be combined into a master table:</p>

<p><strong>Master Table</strong></p>

<pre><code>FRUIT | COLOR  | SHAPE   | DOMAIN
---------------------------------
apple | red    | round   | 0b111
banana| yellow | curved  | 0b110
banana| blue   | straight| 0b001
</code></pre>

<hr>

<p>I have the databases in text file format (i.e. tab seperated lists). I use Python to import them into my sqlite DB.
How would I <em>most efficiently</em> do this merging process?</p>

<p>I have two ideas:</p>

<ol>
<li><p>Import the first DB into the master DB. When importing the next DB, check if the complete record exists. If yes, run an SQL UPDATE query on the applicability column. If not, create new record using INSERT.</p></li>
<li><p>For each type of table load each of the 20 domain-tables in python and see, if a record exists on every domain or a subset. Then import the record with the applicability into the master DB. </p></li>
</ol>

<p>I would like to know if there is an efficient way of performing these operations. Due to the size of each database and the requirement of having to do this import multiple times, I need to speed up the process as much as possible.</p>

<p>Making individual selects and updates for each record*domain is way slower than merging records in memory.</p>

<p>Load 20 tables into memory. Make complete list of records in memory. Bulk insert into DB.</p>

<p>Probably speed-efficient way for merge would be like this:</p>

<p>Load (key,value)->(domain code, null for now) into one set, and separately key->value into domain-table dictionary.</p>

<p>Then go through (key,value) set, look up for value in each domain dictionary and form domain code in master dictionary.</p>

<p>Then go through result of previous step and perform bulk insert, commiting once per about 1000 records.</p>

python database performance sqlite3