I have the following scenario: I have some CSVs to be imported in a Rails application and the size of the dataset can be over 100k rows and this implies a huge amount of memory in use - which I don't have on the server.

Each CSV represent a table dump.  

Now, my problem is that I need to import the data in several tables maintaining the relationships by foreign keys.

What I've done so far is roughly this:

* Create ids cache hashes
* For each CSV/table `find_or_initialize` by an attribute where possible, or perform something like `model.where({complicated conditions}) || model.create({complicated conditions})` saving the created object
* Populate the ids cache mapping `CSV id` => `DB id`

In the `complicated conditions` statement can sits some of the ids saved in previous tables and cached.

Have a look [at the code here](https://github.com/linguisticexplorer/Linguistic-Explorer/blob/master/lib/group_data/importer.rb) for more details.

Note: what I need is more an `upsert` rather than just a plain `insert`.

Some optimisations I've already tried:

* Used transactions => less memory used and quicker to insert
* Used [`crewait` gem](https://github.com/theAlmanac/crewait) => Quicker than plain AR, but slower than transactions
* `model.skip_callbacks(:create)` => speedup or memory improvement not noticeable
* Cached the `user` model which is widely used in all the other tables => high memory use and slower (?!)
* In case a row is already present, select only the `id` attribute to use less memory => not a big difference in speed/memory
* Optimised hash structure for cache: used [Google Hashes](https://github.com/rdp/google_hash) structures to store ids as INT->INT => 10% less memory use

Something else I've looked at but cannot figure out how to use:

* Single and long SQL query: it is basically the idea behind `crewait` but it doesn't work great as far as I tried
* [`activerecord-import`](https://github.com/zdennis/activerecord-import): it's faster to import, but I'll then lose all the relationships or CSV to DB id mapping
* [`upsert`](https://github.com/seamusabshere/upsert): I've seen it but I would like to use it as last resort (it's a bit tricky IMHO).

Any suggestion, recommendation on how to improve is very welcome: talking about tool, library, strategy or whatever.

Update

This is a simplified example of the CSVs I have:

**lings.csv**

```
------------------------
| id | name    | depth |
------------------------
| 0  | English |   0   |
------------------------
| 1  | French  |   0   |
------------------------
| etc..                |
------------------------

```

**properties.csv**

```
-----------------------------------
| id | name         | description |
-----------------------------------
| 0  | Subject_Verb | bla, bla... |
-----------------------------------
| 1  | Verb_Subject | bla, bla... |
-----------------------------------
| etc..                           |
-----------------------------------

```

**lings\_properties.csv**

```
--------------------------------------
| id | value | ling_id | property_id |
--------------------------------------
| 0  | Yes   |    0    |     0       |
--------------------------------------
| 1  | No    |    1    |     1       |
--------------------------------------
| etc..                              |
--------------------------------------

```

Looking at the example above, when I import the Lings and Properties different ids will be assigned to them but I still want the LingsProperties to be linked to English and French thought.
I cannot use the CSV ids in the DB - they are assigned by another application that has a different schema from the one I'm importing them in.

### Update 2

My Rails version is 3.0.20.
I'm moving on Rails 3.2 (or later) where I can use `first_or_create` (or similar) but for the moment I'm in stuck with Rails 3.0.