Implementing postgres inserts using UNNEST syntax

[caccorsi]

Hi, I've been using NHibernate for ~10 years for various projects. I always thought about contributing something but never came across anything I needed the platform didn't offer. Now I'm involved in a project where the bulk insert performance is the primary concern. This project uses Postgres and we experimented with various connection pool and batch settings to eventually reach a plateau in terms of throughput in our tests. We were asked to try and get more throughput and we started researching the use of stored procedures but I was concerned about deployability and maintainability if this approach. Then we recently discovered the UNNEST syntax in postgres and we experimented with direct sql in this UNNEST format.

So instead of this sql generated by the ORM..

--ADO batch size set to 1000
insert into documents values ('id1', 'DocumentType1', 'First Document')
insert into documents values ('id2', 'DocumentType2', 'Second Document')
insert into documents values ('id3', 'DocumentType3', 'Third Document')
insert into documents values ('idN', 'DocumentTypeN', 'Nth Document')

We sent direct sql like this.

--Code assembled this SQL for 1000 items. 
INSERT INTO documents (id, discriminator, description)
SELECT *
FROM UNNEST(
    ARRAY['id1', 'id2', 'id3', 'idN']::TEXT[],
    ARRAY['DocumentType1', 'DocumentType2', 'DocumentType3', 'DocumentTypeN']::TEXT[],
    ARRAY['First Document', 'Second Document', 'Third Document', 'Nth Document']::TEXT[]
) AS data(id, name, description);

And the performance improvement was quite noticeable.
Ideally, we could use for updates/delete as well but my proof of concept would be to implement only the inserts like this.

We have a specific use case where we assign the unique ids at the app layer, so there's no need for a generated unique id. Also in most cases there are no relationships to deal with. The entities are mostly stand alone POCO type objects with primitive fields however there is a TPH strategy where all derived types are in the same table.

My question is, if I wanted to try and implement this in NH, where would be the correct place to start?
A custom EntityPersister? Dialect overrides? ClassBinder?
If you can provide some guidance, I'd like to try this out and would contribute anything back I can get working.
Thanks.

[fredericDelaporte]

Hi,

I do not really think there would be a suitable place for such a change, for many reasons:

• It looks heavily vendor specific (specific to PostgreSQL), so it would have to be heavily bound to the dialect.
• It aggregates the inserts of the entities in a single one, instead of just batching distinct inserts together. That is a lot more complex, beyond what the batcher is meant to do.
• As an ORM, NHibernate is not well suited for bulk operations. An ORM has some overhead for handling entities semantics, which is acceptable for operations on reasonable numbers of entities, but usually is a showstopper for massive operations. Optimizing for bulk operations is not considered a goal for NHibernate. Other tools are better suited.

[caccorsi]

Hi Frédéric, thank you for the response, I’m honored 😀. We’re using many amazing features oh NH and leveraging several of extensibility points for a transactional data access layer. It’s fantastic! However there are migrations from legacy products to deal with, hence the bulk requirement. I fully appreciate the position you’re taking on my proposal. As a consolation,  can you think of anything that might help a large batch of inserts that don’t require a session state?Best,Carlo AccorsiOn Nov 18, 2024, at 2:34 PM, Frédéric Delaporte ***@***.***> wrote: Hi, I do not really think there would be a suitable place for such a change, for many reasons: It looks heavily vendor specific (specific to PostgreSQL), so it would have to be heavily bound to the dialect. It aggregates the inserts of the entities in a single one, instead of just batching distinct inserts together. That is a lot more complex, beyond what the batcher is meant to do. As an ORM, NHibernate is not well suited for bulk operations. An ORM has some overhead for handling entities semantics, which is acceptable for operations on reasonable numbers of entities, but usually is a showstopper for massive operations. Optimizing for bulk operations is not considered a goal for NHibernate. Other tools are better suited. —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[fredericDelaporte]

NHibernate allows execution of SQL queries, see here. So, if you were willing to code something into NHibernate to generates an optimized batched query, you may consider instead generating it on your application side then executing it through that interface. NHibernate has very little value here, though. It merely gets a connection and executes a command on it.

There is also the stateless session, which may perform better for bulk operations, but no guarantees. It can perform worse too.