Whats the fast way to update multiple entries in the db

I wrote a function that activates a batch of cards based on there pan number

The way it works:
→ A user inserts an excel file with a list of card pans that are already in the system plus user details, the system validates the details and update the card with the user details

When the user makes a list of a more than 500 excel entires, the app takes more the 4 minutes to update the cards.

This is the way I’m updating the cards after its extracted

list
|> Enum.map(fn x ->
    Ecards.find_by(pan: x.pan)
    |> case do
        nil -> # flag fail
        card ->
           Ecards.update(card, firstName: x.firstName, lastName: x.lastName, cardStatus: "activate")
              # flag success
     end

end)

# count success and failed then display to user

Is there a faster way of updating the cards from the list that meet the criteria?

1 Like

Is Ecards.update doing anything special? 240 seconds to do 500 SQL updates is very slow

You definitely want to use Ecto.Transaction, Ecto.Multi and Ecto.update_all for bulk data modifications rather than updating individually.

https://hexdocs.pm/ecto/composable-transactions-with-multi.html

extensive examples are given in the documentation

2 Likes

No its not, I think the https://hexdocs.pm/endon/Endon.html library is meant for simple db queries not bulk queries

Endon is an Elixir library that provides helper functions for Ecto, with some inspiration from Ruby on Rails’ ActiveRecord.

oh dude, AR is a massively flawed concept that you should walk away as soon as possible.

It forces you to create such intertwined messy code on top of hitting this kind of performance problems with minimal code in a tiny app early on.

1 Like

Debates about the pattern that Endon introduces aside, all update is doing is:

which tells me there’s two possibilities:

  • either find_by is really slow
  • or Repo.update is really slow

In either case, update_all and multis will not help much - you’ll need to debug the actual issue.

I’d start by looking at the find_by: how many rows are in that table? Is pan indexed?

2 Likes