JDBC Performance Mini-Sticker

This article collects some methods for improving the performance of JDBC. The performance of a Java application or EE Web application is important, especially the performance impact of the database backend on the application. I don't know if you've ever experienced Java, a very slow case of EE Web applications (it takes several seconds to process a simple request for database access, paging, sorting, and so on). The following tips may improve the performance of your Java application. They are very simple and can also be applied to other programming languages, if the database is used as back-end storage.

These JDBC performance stickers are not necessarily cool or some you have never heard of, although the basics are very basic but in practice many programmers often ignore them, and of course you may refer to the title as a database performance hint.

JDBC Performance tip one: Using caching

Find out How many database calls are in your app, and then minimize them, whether you believe it or not. The main culprit in most cases is the code that accesses the database. Because connecting to the database requires a ready connection (connections), there is also a round-trip network transfer and database system backend processing. If you can cache the data, this is the best way to reduce the database calls, even if your application has completely dynamic data, a short cache can save a lot of data hard-to-return transmission. Accelerating Java applications can at least reduce 20-50% database calls, and if you want to find database calls, simply log each DB call in the DAO layer to the logs, and if it's better to log each thread into and out of the database access, it can tell you how much time it takes to get a call.

JDBC Performance Tip Two: Working with database indexes

Check that the database column (columns) has an index, and if you are doing a query discovery that takes longer than expected, the first thing you'll think about is whether the column (the column in the WHERE clause you're querying) is indexed. Programmers often make this mistake, with indexes and no indexes that make a huge difference when doing queries. This tip can be at least 100% faster in performance, and of course the right index is more important, and too many indexes slow down data insertion and update operations. So be careful when using indexes, such as ID, category, class, and so on, the index is often used.

JDBC Performance tip three: Using PreparedStatement

When you execute a query using PreparedStatement or a stored procedure (Stored Procedure), the PreparedStatement (preprocessing statement) is faster than the normal statement object. This is because the database can pre-process the query to a query cache plan. Therefore always use * * parameterized form of pre-processing statements * * such as SELECT * FROM table WHERE id=? , rather SELECT * FROM table WHERE id=‘"+id "‘ than use, although the latter is still a preprocessing statement but not parameterized. There is no performance advantage with the second query, and more references can be found to see why JDBC uses preparedstatement instead of statement

JDBC Performance tip Four: Using a database connection pool

Connection pooling is used to hold database connections (Connection), and creating a database connection is a slow process and can take a long time. So if each request is to create a connection, then obviously the response time will be longer. Use connection pooling to create an appropriate number of connections based on upstream traffic and the number of concurrent requests. Even though the connection pool creates a connection in the beginning of the request, the cache connection is slow, but overall it can reduce the overhead.

JDBC Performance tip Five: Batch update with JDBC

Using the JDBC Bulk Update operation can significantly improve the performance of Java database applications. You should always use bulk processing to perform insert and update operations. Do bulk queries by using statment or PreparedStatement. Use the ExecuteBatch () method to do batch queries.

JDBC Performance tip VI: Cancel Auto-commit

Set at query time setAutoCommit(false) , the default JDBC Connection autocommit mode is open, meaning that each individual SQL statement will be executed in its own transaction. However, you can group SQL statements into a logical transaction, either by calling commit() or by rollback() either committing or rolling back. Try running the same number of queries compared to how different performance differences are when using autocommit and not using autocommit.

These Java database application performance tips look very simple, many advanced Java programmers are skilled in production-level code, but I also see a lot of Java programmers do not care about this until they find that Java applications have become very slow. So for beginners it is necessary to remember that it uses it. You can also use these Java performance tips as a reference for the code review Mechanism, when you write a Java application that uses a database as a backend store.

Here's a tip on how to improve the performance of your Java application, and if you have any better tips, you might want to leave a message.

Acquisition

JDBC Performance Mini-sticker