Syncing data between CSV and DB

user_7659860 0
Viewed 32

I need sync data between a CSV file and my DB, but is a very slow process when I check if each item exists.

For example, I have a very large list of postal codes, when this list load into the system, the app need check if this record exist in the database.

I try to use find_or_initialize_by, but is very slow when the list of postal codes has more than 100_000 records ... I also tried to cache all the records in the database and compare them using .select, but it is almost as slow as using the database.

Any suggestion?

ruby-on-rails ruby
1 Answers

Using find_or_initialize_by is extremely slow for use cases like these because this approach would run at least one query against the database for each line in the CSV file. And if the record wasn't found there will be a second insert query. Even if every single query is extremely fast, let's assume they only take 5ms, they will add up: With 100k lines in the CSV alone the find_or_initialize_by method calls will take over 8 minutes.

Therefore my approach would be to avoid doing many small database queries and instead do only a few, big queries and keep the data in memory.

First, load all records from the database but not the whole record but only the unique parts. For postal code data that might be the zip_code column. Then store that data in an in-memory data structure that allows fast lookup, for example in a Set.

require 'set'

existing_zip_codes = Set.new(
  PostalCode.all.pluck(:zip_code)
)

Then iterate over the CSV and collect all data that need to be imported into the database.

missing_postal_codes = []

CSV.foreach(...) do |row|
  next if existing_zip_codes.include?(row['zip_code'])

  missing_postal_codes << {
    zip_code: row['zip_code'],
    city: row['city'],
    # ...
  }
end

And in the last step, I would insert all those missing data with one big insert_all call into the database.

PostalCode.insert_all(missing_postal_codes)
user_24833130
Related