Fastest MS SQL Inserts — Part 5: Hibernate Tuning

In Part 1 we watched Spring Data's saveAll() take 7x longer than batched persist() because every entity triggers a SELECT before its INSERT. In Part 2 we switched to raw JDBC and cut the time further. But Hibernate still sits well behind — so how close can it actually get to the bare metal?

Three settings close 80% of the gap: implement Persistable to kill the pre-INSERT SELECTs (8x faster saveAll), use StatelessSession to drop the first-level cache (35% faster persist), and layer useBulkCopyForBatchInsert=true on top (another 2–2.5x). Combined with proper batch sizing, this is as fast as Hibernate gets.

The saveAll Problem: SELECT Before INSERT

When you call repository.saveAll(entities), Spring Data JPA checks each entity's ID. If the ID is non-null, it assumes the entity might already exist and calls EntityManager.merge() — which issues a SELECT before the INSERT. For 100,000 entities, that's 100,000 extra SELECTs.

Fix: Implement Persistable

The fix is simple: make your entity implement Persistable<T> and always return true from isNew():

 1@Entity
 2@Table(name = "person")
 3public class PersistablePerson implements Persistable<Integer> {
 4    @Id
 5    private int personId;
 6    private String userName;
 7    private String firstName;
 8    private String lastName;
 9    private int years;
10
11    @Transient
12    private boolean isNew = true;
13
14    @Override
15    public Integer getId() { return personId; }
16
17    @Override
18    public boolean isNew() { return isNew; }
19
20    @PostPersist
21    @PostLoad
22    void markNotNew() { this.isNew = false; }
23}

When isNew() returns true, Spring Data uses persist() (direct INSERT) instead of merge() (SELECT + INSERT). The @PostPersist and @PostLoad callbacks reset the flag after the entity is saved or loaded from the database.

Results

Persistable makes saveAll 8x faster — and now slightly faster than manual persist-in-chunks! The SELECT elimination is the single biggest win.

StatelessSession: Bare-Metal Hibernate

Hibernate's StatelessSession is designed for bulk operations. It bypasses:

• First-level cache — no entity state tracking, constant memory
• Dirty checking — no scanning managed entities at flush time
• Cascading — related entities must be inserted explicitly

 1@Repository
 2public class StatelessSessionInsertRepository {
 3    private final EntityManagerFactory entityManagerFactory;
 4
 5    public void insertStateless(List<Person> people, int batchSize) {
 6        SessionFactory sf = entityManagerFactory.unwrap(SessionFactory.class);
 7
 8        try (StatelessSession session = sf.openStatelessSession()) {
 9            session.getTransaction().begin();
10            session.doWork(conn -> conn.createStatement().execute("SET NOCOUNT ON"));
11
12            for (int i = 0; i < people.size(); i++) {
13                session.insert(people.get(i));
14            }
15
16            session.getTransaction().commit();
17        }
18    }
19}

Results

StatelessSession is 35% faster than regular Hibernate persist and within striking distance of raw JDBC. The remaining gap (~10%) is Hibernate's SQL generation and parameter binding overhead.

The Killer Combo: StatelessSession + useBulkCopyForBatchInsert

Remember the useBulkCopyForBatchInsert=true connection string parameter from Part 4? It transparently converts PreparedStatement batches to SQL Server's Bulk Copy protocol. It works with StatelessSession too:

StatelessSession + bulkCopy=true = 1,075ms — that's just 2x slower than the raw Bulk API (492ms), while still using Hibernate's mapping metadata. For applications that need ORM convenience with near-JDBC performance, this is the sweet spot.

provider_disables_autocommit

The hibernate.connection.provider_disables_autocommit setting tells Hibernate that the connection pool already disabled auto-commit, skipping the setAutoCommit(false) JDBC round-trip per transaction. To use it, you need both:

1spring.jpa.properties.hibernate.connection.provider_disables_autocommit=true
2spring.datasource.hikari.auto-commit=false

Verdict: negligible for persist, modest for saveAll. The saved round-trip is only measurable when there are many small transactions (saveAll with frequent flushes) rather than one large batch (persist). Not worth the configuration complexity unless you have a very specific transactional pattern.

Batch Size Sweet Spot

Parts 1-4 tested batch sizes of 10, 100, 1000, and 5000. A fine-grained sweep (50-2000 in steps of 50) reveals the actual inflection points:

Bulk API

Bulk API: diminishing returns past 500. The sweet spot is 500-2000 with wide variance (single-run noise). Use 1000 as a safe default.

JdbcTemplate

JdbcTemplate: optimal at 700, with minimal improvement past 500. Larger batches (1000+) don't help because the driver's TCP buffer and SQL Server's batch processing saturate around 500-800 rows.

Multi-Value Sweet Spot

Part 3 showed that multi-value INSERT (objectsPerInsert > 1) is much faster than single-row inserts. But what's the optimal value? A fine-grained sweep reveals:

Optimal: 200 values per INSERT. After 200, SQL Server's query parser overhead for the growing SQL string outweighs the network round-trip savings. The sweet spot is 100-200 — enough to amortize per-statement overhead without overwhelming the parser.

Remember: SQL Server's maximum parameter count is 2,100. With 5 columns per row, objectsPerInsert cannot exceed 420 (2100/5 = 420).

Connection String: sendStringParametersAsUnicode

Setting sendStringParametersAsUnicode=false in the JDBC URL tells the driver to send string parameters as VARCHAR instead of NVARCHAR. This can help when your table has VARCHAR columns (avoids implicit conversion), but has no effect on NVARCHAR columns:

If your columns are NVARCHAR, skip this setting.

Transaction Patterns

How you structure transactions matters more than how big they are:

Verdict: auto-commit per row is catastrophic — each INSERT becomes its own transaction with its own log flush. Any explicit transaction (batched or whole-run) is dramatically faster. The difference between commit-per-batch and commit-at-end is noise.

Isolation Levels

Isolation level has no measurable impact on INSERT-only workloads. The isolation level primarily affects reads and locking behavior — there's nothing for an INSERT to "read dirtily" or "serialize against". Don't bother tuning isolation level for bulk-load performance.

GC and JVM Memory Tuning

Does the Java garbage collector affect database insert throughput? We ran the same test with four GC/heap configurations:

GC choice has essentially no impact on insert performance for this workload. The reasons:

1. Short-lived objects fit in young generation — Person instances die quickly after being batched and sent to the driver. All GCs handle this well.
2. GC pauses are tiny compared to database I/O (300ms of GC vs 30+ seconds of total time).
3. ZGC has slight overhead — its concurrent collection isn't needed when heap pressure is low.

Takeaway: stick with the default G1 and modest heap size (512 MB-1 GB is enough for 100k-row batches). Over-tuning the JVM for database workloads is a waste of time — the bottleneck is the network/disk, not Java.

The one thing to remember

StatelessSession + useBulkCopyForBatchInsert=true = 1,075 ms for 100k inserts — 2x slower than raw Bulk Copy (492 ms), with ORM mapping metadata still working for you. If you need Hibernate's convenience without paying Hibernate's full price, that's the combination.

If you can only make one change to speed up a saveAll() call: implement Persistable<T> with isNew = true. It eliminates the pre-INSERT SELECTs and makes saveAll 8x faster — a 10-line code change for a 10x result.

Next: Part 6 — Parallel Inserts splits the data across multiple connections to see how far parallel threads can push it.