databases Inserting to database using execute_many much slower than using fetch

Hi,

I noticed that inserting multiple rows to postgres database with execute_many is much slower than with fetch_all

"inserting with execute_many": "Time (hh:mm:ss.ms) 0:00:02.538554",
"inserting with fetch_all": "Time (hh:mm:ss.ms) 0:00:00.249998",

Feb 02 '21 12:02 psowa001

execute_many does not currently invoke executemany of the Postgres driver. I don't know why :smile: I have this in my code:

class ExecuteManyConnection(databases.core.Connection):
    """Connection with a better execute_many()."""

    async def execute_many(self,
                           query: Union[ClauseElement, str],
                           values: List[Mapping]) -> None:
        """Leverage executemany() if connected to PostgreSQL for better performance."""
        if not isinstance(self.raw_connection, asyncpg.Connection):
            return await super().execute_many(query, values)
        sql, args = self._compile(query, values)
        async with self._query_lock:
            await self.raw_connection.executemany(sql, args)

    def _compile(self, query: ClauseElement, values: List[Mapping]) -> Tuple[str, List[list]]:
        compiled = query.compile(dialect=self._backend._dialect)
        compiled_params = sorted(compiled.params.items())

        sql_mapping = {}
        param_mapping = {}
        for i, (key, _) in enumerate(compiled_params):
            sql_mapping[key] = "$" + str(i + 1)
            param_mapping[key] = i
        compiled_query = compiled.string % sql_mapping

        processors = compiled._bind_processors
        args = []
        for dikt in values:
            series = [None] * len(compiled_params)
            args.append(series)
            for key, val in dikt.items():
                series[param_mapping[key]] = processors[key](val) if key in processors else val

        return compiled_query, args

It seems to work fine in our production. If you PR this change, I'll happily merge it (I cannot merge my own PRs, and getting a review from another maintainer is almost impossible).

Feb 02 '21 15:02 vmarkovtsev

@vmarkovtsev what is going on with the other maintainers?

Feb 02 '21 16:02 julian-r

They just disappeared haha. I had to spend a titanic effort to make a new release last time. My own PR is hanging since June 2020, for example. My guess is that the original author is using something different now, or changed jobs.

While I have solved many problems that people reported here: performance, locks, etc., I don't have enough time to properly fork the project.

Feb 02 '21 17:02 vmarkovtsev

With the latest asyncpg v0.22 release: https://github.com/MagicStack/asyncpg/releases/tag/v0.22.0, this might be a good timing to upgrade execute_many to invoke the vastly improved executemany()