Question about Ecto insert_all

Questions / Help
,
1

Hi everyone. When running Ecto.insert_all([list_of_maps]) will ecto send one request to DB, or one insert request per map in the list? Looking at DB metrics it seems the second option is happening.

If that’s the case, is there a way to insert a list of a few thousand entities inside one query/transaction?

Thanks in advance

1 Like
2

I am not an expert in Ecto, but you could possibly build up a sql string using “insert” statement with many values clauses.

1 Like
3

It’s one query. It’s the same as INSERT INTO table VALUES (...), (...), ..., (...)

4 Likes
4

Thanks. I saw a DB metric called “operations per second”, and it went through the roof (1k+ ops/s) when I executed this Ecto.insert_all.

Now I’m pretty sure that ops/s measures read/write operations, and although Ecto, as you pointed out, generated one INSERT statement, DB had to perform one “write to disk” operation for each entry in the INSERT statement.

5

I think that’s probably what’s happening as well. I have similar metrics where you can see each row being read even if they are not returned by the result. It is helpful finding inefficient queries.