PreparedStatement how much improved performance
Author: Billy Newport
This article tells how to use Prepared Statements correctly. Why it allows your app to run faster, and the same make the database operation faster.
Why is Prepared Statements very important? How to use it correctly?
The database has a very difficult job. They accept SQL queries from many concurrent clients and perform queries as quickly as possible. Handling Statements is an expensive operation, but now there is a method of prepared Statements that minimizes this overhead. However, this optimization requires developers to complete. So this article will show you how to use Prepared Statements in order to make the database operation to optimize.
How is the database executes a Statement?
Obviously, I will not write a lot of details here, we only pay attention to the most critical part. When a database receives a statement, the database engine will first resolve Statement and check if it has a syntax error. Once Statement is correctly parsed, the database will select the optimal way to perform Statement. Unfortunately, this calculation overhead is very expensive. The database will first check if there is a related index to help this, whether or not all of the rows in a table will be read. The database statistics on the data and then elects the optimal way. After the query scheme will be created, the database engine will execute it.
The generation of Access Plan will occupy considerable CPU. Ideally, when we send a Statement to the database multiple times, the database should be reused at the STATEMENT access scheme. If the solution has been generated, this will reduce the use of CPUs.
Statement Caches
A database has a similar function. They usually use the following method to cache. Use Statement itself as Key and store the access scheme into the cache corresponding to Statement. Such a database engine can reuse the access schemes in Statements once performed. For example, if we send a statement to the database containing SELECT A, B from T where c = 2, then first cache the access scheme. When we send the same Statement again, the database is reused for previously used access schemes, which reduces the overhead of the CPU.
Note that the entire Statement is used here. That is, if we send a statement containing SELECT A, B from T where c = 3, there will be no corresponding access schemes in the cache. This is because "c = 3" is different from "C = 2" that has been cached. So, give an example:
For (int i = 0; i <1000; i ) {
PreparedStateMent PS = Conn.PrepareStatement ("SELECT A, B from T where C =" i);
ResultSet RS = ps.executeQuery ();
Rs.close ();
ps.close ();
}
The cache is not used here, because each iteration sends a statement containing different SQL statements to the database. And each iteration generates a new access scheme. Let us now take a look at the next code:
PreparedStatement ps = conn.preparestatement ("SELECT A, B from T where c =?");
For (int i = 0; i <1000; i ) {ps.setint (1, i);
ResultSet RS = ps.executeQuery ();
Rs.close ();
ps.close ();
}
This has a better efficiency, which is sent to the database for a SQL statement with parameters "?". This way each iteration will send the same statement to the database, just the parameter "c =?". This approach allows the database to recover the STATEMENT access scheme, which has better efficiency. This allows your application speeds faster and uses fewer CPUs so that the database server can serve more people.
PreparedStatement with J2EE Server
The matter will change when we use the J2EE server. Typically, a perpared statement associates with a separate database connection. PREPARED Statement will also be discarded when the database connection is turned off. Typically, a fat client will get a database connection and keep it until exit. It creates all ParePared Statements in "Eagerly" or "Lazily". "Hungry Han" mode creates everything when the application is started. The "lazy" mode means that only when you are using it. "Hungry" mode will make the app there is latency when it is started, but once it will run, it will run quite ideals. "Slayer" mode makes the application very fast (but not for any preparations), when you need to use Prepared Statement, you will be created again. In this way, performance is very unstable during the creation of all statement, but once all statements have been created, it will have a good running effect like "Hungry" applications. Please choose the best way according to your needs, is it fast start? Still the performance of the front and rear.
The problem with J2EE application is that it will not work like this, the connection will only be held during the request. That means you have to create prepared statement every time you request. This has no fat client that keeps prepared stat's performance. J2EE manufacturers have noticed this issue and provide connection pools to avoid this problem.
When the J2EE server provides a connection to your application, it did not give you a real database connection, you just got a wrapper. You can see the class name you get to confirm this. It is not a JDBC connection, but a class created by the application server. All JDBC operations will be proxy by the application server's connection pool manager. All JDBC RESULTSETS, Statements, Callablestatements, prepaaredStatements, etc. are wrapped and returned to the application in the form of a "proxy object". When you close the connection, these objects are marked as invalid and are reclaimed by the garbage collector.
Typically, if you execute Close to a database connection, this connection is turned off by the JDBC driver. But we need to return the connection pool when the J2EE server performs Close. We can create a JDBC Connection proxy class like a real connection to solve this problem. It has a reference to the real connection. When we perform a connection method, the agent will turn the action to the real connection. However, when we execute a Close to a connection, this connection does not turn off, but it will be sent back to the connection pool and can be used by other requests. A prepared prepared statement will also be reused.
The J2EE PreparedStatement Cachej2ee server's connection pool manager has implemented the cache. The J2EE server maintains a list of Prepared Statement in each connection in the connection pool. When we call prepaaredStatement on a connection, the application server checks if this Statement has been prepared. If so, this preparedStatement will be returned to the application. If no, the call will be forwarded to the JDBC driver and then store the newly generated Statement object into the connection cache.
The reason for each connection has a cache because the JDBC driver works like this. Any Prepared Statement is returned by the specified connection.
If we want to take advantage of this cache, as mentioned earlier, use the parametric query statement to find the Statement used to use in the cache. Most application servers allow you to adjust the size of the Prepared Statements cache.
Summary
We will definitely use the prepared statement that contains the parameterized query statement. This database will reuse the prepared access scheme. The cache applies to the entire database, so if you arrange all the applications to use the same parameterized SQL statement, then your other applications can reuse the prepared prepared statement. This is an advantage of the application server because all database operations are concentrated in the database operation layer (Database Access Layer, including O / R mapping, entity beans, JDBC, etc.).
Second, the correct use of Prepared Statement is also the key to the cache advantage of Prepared Statement. Since the application can reuse the prepared Prepared Statement, the number of calls to the JDBC driver is reduced, thereby increasing the performance of the application. This has the efficiency that can be with the fat client, but no need to maintain a connection.
With parameterized Prepared Statement, your application will have better performance.
