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.
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.
The fastest way is through JDBC batch inserts:
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.
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.
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.
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
