CODEWITHSUNDEEP
javamysqljdbc
codecompleting
codecompleting

Reputation: 9611

Fastest way to insert into MySQL in Java

I have a service endpoint that basically inserts some data into mysql. I`m looking for the fastest way to insert a row into mysql.

I don`t mind if it is very verbose or if it takes longer to make changes (compared to say using hibernate) as the scope of this servlet is very minimal, but performance is top priority really.

I`m newish to java/servlets so please don't leave any details out or assume to much on my end.

Upvotes: 1

Views: 6958

Answers (6)

Joshua Martell
Joshua Martell

Reputation: 7212

If you're only inserting a single row, a simple insert, possibly prepared query, is likely fastest. Use a connection pool so you don't have to connect each time, and there are tuning parameters on the database that will make a difference.

If you can get away with it, I would suggest not actually doing the insert in the servlet, but just putting the data into a memory queue that is inserted into the database by another thread. You won't know if it succeeded or not in the servlet response, though.

http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Upvotes: 0

Puce
Puce

Reputation: 38132

If you're not using JPA, I recommend to use JdbcTemplate from Spring (even if you don't use Spring otherwise) to manage the connections (closing them etc.)

Use PreparedStatements.

Don't initialize the driver/ connections directly. Rather use a pooled DataSource configured in your application server (you can inject it with @Resource).

Use EJBs for transaction management.

Upvotes: 0

Rostislav Matl
Rostislav Matl

Reputation: 4543

There is comparison of insert speeds using JDBC and different methods. It is for PostgreSQL but the approach and results will be similar for MySQL: http://rostislav-matl.blogspot.com/2011/08/fast-inserts-to-postgresql-with-jdbc.html .

In short: used batched inserts (supported probably in any JDBC driver) and where available consider COPY FROM.

Upvotes: 2

Michael Borgwardt
Michael Borgwardt

Reputation: 346310

Be sure to use a connection pool or something similar. This is by far the biggest factor.

It might also help to use a PrepraredStatement that actually persists across servlet requests in order to spare MySQL from parsing the request every time.

Both together could be implemented by simply keeping the DB connection as well as the PreparedStatement in a ThreadLocal, but that assumes that the servlet container uses a thread pool.

Update: c3p0 apparently implements both connection and statement pooling, so I'd just use that.

Upvotes: 0

santiagobasulto
santiagobasulto

Reputation: 11686

The fastest way is through JDBC batch inserts:

http://www.roseindia.net/jdbc/Jdbc-batch-insert.shtml

Upvotes: 2

Mathias Schwarz
Mathias Schwarz

Reputation: 7197

The fastest way is probably to use a JDBC driver directly and write the SQL yourself. Here is some sample code that looks fine to me: http://www.roseindia.net/jdbc/jdbc-mysql/InsertValues.shtml

People use Hibernate for many reasons. Other than making it much simpler to map classes to database tables it also handles caching, connection pooling and stuff like that. Opening a new database connection takes a lot of time, so you will have to implement stuff like that yourself to make you servlet perform well.

Upvotes: 4

Related Questions