CODEWITHSUNDEEP
javasqlperformancejdbc
Bhabani Sankar Mishra
Bhabani Sankar Mishra

Reputation: 523

Optimum number of insert queries in a Batch Execute

I am running some insert queries using JDBC's executeBatch method. Let me first explain a little bit about the application. There's a web app which will send some insert statements to a cache in async way when user navigates through some webpages. When the size of the cached queries grows to certain number, say 'n', the queries will be executed as a batch using the executeBatch method of JDBC.

I am not really from a DB background and not sure how to come up with the value of 'n'. What all factors will determine it and how do I figure out some approximate value mathematically before doing load test.

As far as usage is concerned, I am expecting around 72000 insert queries in an hour and the priority is the smooth running of the web app and not insertion of the data.

Please suggest.

Edit - update number of queries to 72000 per hour from 2000, my apologies, had wrong data before :(

Upvotes: 1

Views: 1389

Answers (2)

Aaron Digulla
Aaron Digulla

Reputation: 328614

Your question doesn't contain any useful data to answer this question. The speed of an insert depends on:

  1. Size of the row (i.e. the limits that you told the database)
  2. Number of indexes touched by the insert
  3. Some types like (C)LOB are more expensive than others
  4. Actual amount of data per row
  5. The database you use
  6. Latency
  7. Layout of the table storage on disk

As a rule of thumb, we join 10000 rows for small inserts (2-3 columns, < 100 bytes for the whole row), 1-2000 for normal rows (< 1KB each) and 1 for huge rows (> 100K)

EDIT Write a small performance test that inserts 1 millions records with different batch sizes and time it. Anything else is just guesswork.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

2000 insert queries an hour is about one every 1.5 seconds. This is not a serious load on any database running on any modern device. The only exception would be for inserts are inordinately long, such as storing megabyte-length blobs or inserts that might have long trigger chains doing complex work.

I suspect that attempting to optimize this facet of the application is premature. You should be able to do a separate insert for each operation without affecting performance. This will also simplify the application. By not using a cache, you don't have to worry about maintaining consistency with the cache, what to do if the application side fails, what to do if a second cache update occurs before the previous one has ended, and so on. You also don't have to worry about how to handle the caching when the cache is actually being updated.

I should note that caching updates can have some negative effects, in terms of locking tables/pages for longer periods of time. Overall, they are more efficient (if only because there is only one round trip to the database if done correctly). However, the efficiency may mask longer periods of objects being locked in the database.

Upvotes: 3

Related Questions