How the PreparedStatement greatly improves performance

Transfer from Billy Newport

This article describes how to use the prepared statements correctly. Why it can make your application run faster, and the same to make the database operation become faster.


Why is prepared statements important? How to use it correctly?

The database has very hard work. They accept SQL queries from many concurrent clients and execute queries as quickly as possible and return results. Handling statements is an expensive operation, but now with prepared statements, you can minimize this overhead. But this optimization needs to be done by the developer. So this article will show you how to properly use prepared statements to optimize the operation of the database.


How does a database perform a statement?

Obviously, I'm not going to write a lot of details here, we just focus on the most critical part. When a database receives a statement, the Database Engine resolves statement First and then checks for syntax errors. Once the statement is correctly parsed, the database will select the best way to execute the statement. Unfortunately, the computational overhead is very expensive. The database first checks to see if there are related indexes that can help, regardless of whether all the rows in a table are read out. The database counts the data and then chooses the optimal path. When the query scheme is created, the database engine executes it.

The generation of access plan consumes a considerable amount of CPU. Ideally, when we send a statement to the database multiple times, the database should be reused for the statement access scheme. This will reduce CPU usage if the scenario has ever been generated.


Statement Caches

The database already has a similar function. They typically cache statement in the following ways. Use the statement itself as key and store the access scheme in the cache corresponding to the statement. This allows the database engine to reuse the access schemes in the statements that have been executed. For example, if we send a statement to a database that contains select a, b from t WHERE C = 2, then the access scheme is first cached. When we send the same statement again, the database reuses the previously used access scheme, which lowers the CPU overhead.

Note that the entire statement is used here as key. That is, if we send a statement that contains select a, b from t WHERE C = 3, there is no corresponding access scheme in the cache. This is because "c=3" differs from the "c=2" that was previously cached. So, let's take an example:

for (int i = 0; i <; 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 that contains a different SQL statement to the database. And each iteration generates a new access scheme. Now let's take a look at the next piece of code:

PreparedStatement PS = conn.preparestatement ("Select a B from t where C =?");
for (int i = 0; i <; i++) {
Ps.setint (1, i);
ResultSet rs = Ps.executequery ();
Rs.close ();
Ps.close ();
}

This will be more efficient, the statement sent to the database is a parameter "?" "SQL statement. So each iteration sends the same statement to the database, just the parameter "c=?" Different. This approach allows the database to reuse the statement access scheme, which makes it more efficient. This can make your application faster and use fewer CPUs so that the database server can serve more people.


PreparedStatement and Java EE server

Things can become more complicated when we use the Java EE server. Typically, a perpared statement is associated with a separate database connection. Prepared statement is also discarded when the database connection is closed. Typically, a rich client gets a database connection and keeps it out until it exits. It creates all parepared statements in "A Hungry Man" (eagerly) or "lazy" (lazily) mode. The "A Hungry Man" method creates everything when the app starts. "Lazy" means to create only when used. The "A hungry man" approach causes the application to have a delay when it starts, but it is ideal to run once it is started. The "lazy" way makes the application start very fast (but does not do any preparation) and is created when you need to use prepared statement. In this way, performance is very volatile during the creation of all statement, but once all the statement have been created, it will work as well as the "a Hungry Man" application. Please choose the best way according to your needs, is the quick start? and consistent performance.

The problem with the Java EE application is that it does not work like this, and connections are kept only during the request. That means that prepared statement must be created every time a request is made. This is far from being a fat client that keeps prepared statement performing well. The Java EE vendor has noticed the problem and has provided a connection pool (ConnectionPool) to avoid this problem.

When the Java EE server provides a connection to your application, in fact it does not give you a real database connection, you just get a wrapper (Wrapper). You can check the class name of the connection you obtained to confirm this. It is not a JDBC connection, but rather a class created by the application server. All JDBC operations will be proxied by the application server's connection pool manager. All JDBC Resultsets,statements,callablestatements,preparedstatements are packaged and returned to the application in the form of a proxy object. When you close the connection, the objects are flagged as invalidated and reclaimed by the garbage collector.

Typically, if you perform close on a database connection, the connection is closed by the JDBC driver. But we need to perform close on the Java EE server when the database connection is returned to the connection pool. We can create a JDBC connection proxy class like a real connection to solve this problem. It has a reference to a true connection. When we execute a method on a connection, the agent forwards the operation to the true connection. However, when we perform close on a connection, the connection is not closed, but it is sent back to the connection pool and can be used by other requests. A prepared statement that has been prepared will also be reused.


Java EE preparedstatement Cache

The connection pool manager for the Java EE Server has implemented cache usage. The Java EE server maintains a list of prepared statement that are prepared for each connection in the connection pool. When we call PreparedStatement on a connection, the application server checks to see if the statement has been prepared. If so, the PreparedStatement will be returned to the application. If no, the call is forwarded to the JDBC driver and then the newly generated statement object is stored in the connection cache.

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 parameterized query statements to find the statement that have been used in the cache. Most application servers allow you to adjust the size of the prepared statements cache.


Summary

We should definitely use prepared statement, which contains parameterized query statements. The database will then reuse the prepared access scheme. The cache applies to the entire database, so if you schedule all your applications to use the same parameterized SQL statements, then your other applications can reuse the prepared prepared statement. This is an advantage of the application server, because all database operations are centralized at the database operations layer (databases Access layer, including O/R mappings, entity Bean,jdbc, and so on).

Second, the correct use of prepared statement is also the key to leveraging prepared statement's caching advantage. Because the application can reuse the prepared prepared statement, it also reduces the number of calls to the JDBC driver, which improves the performance of the application. This has the ability to be as efficient as a fat client, without having to maintain a connection.

With parameterized prepared statement, your application will have better performance.

///////////////

1.PreparedStatement is pre-compiled and can greatly improve efficiency for batch processing. Also known as the JDBC stored procedure
2. Use the Statement object. When only one-time access to the database is performed, the Statement object is used for processing. The overhead of PreparedStatement objects is larger than statement, and there is no additional benefit for one-time operations.
3.statement each execution of SQL statements, the relevant database to execute SQL statement compilation, PreparedStatement is precompiled, PreparedStatement support batch processing
4.
Code Fragment 1:

String updatestring = "UPDATE coffees SET SALES =" + "WHERE cof_name like′colombian′";
Stmt.executeupdate (updatestring);

Code Fragment 2:

PreparedStatement updatesales = con.preparestatement ("UPDATE coffees SET SALES =?") WHERE cof_name like? ");
Updatesales.setint (1, 75);
Updatesales.setstring (2, "Colombian");
Updatesales.executeupdate ();

The difference between fragment 2 and fragment 1 is that the latter uses the PreparedStatement object, whereas the former is a normal statement object. The PreparedStatement object not only contains the SQL statement, but in most cases the statement has been precompiled, so when it executes, only the DBMS runs the SQL statement without having to compile it first. When you need to execute statement objects multiple times, the PreparedStatement object will greatly reduce the run time and, of course, speed up the access to the database.
This conversion also provides you with great convenience, eliminating the need to repeat the syntax of the SQL statement, and simply changing the value of the variable in it to re-execute the SQL statement. The choice of the PreparedStatement object is whether the SQL statement with the same syntax executes multiple times, and the difference between the two times is only the difference between variables. If it is executed only once, it should be no different from the ordinary object, and it cannot demonstrate the superiority of its precompilation.
5. The JDBC program that executes many SQL statements produces a large number of statement and PreparedStatement objects. PreparedStatement objects are generally considered more efficient than statement objects, especially if the same SQL statement with different parameters is executed multiple times. The PreparedStatement object allows the database to precompile SQL statements, which saves time and increases the readability of the code in subsequent runs.

However, in an Oracle environment, developers actually have greater flexibility. When you use the statement or PreparedStatement object,Oracle database is cached (and JDBC cache is different)SQL statements for later use. In some cases, executing a PreparedStatement object can actually take longer because the drive itself requires additional processing and increased network activity between Java applications and Oracle servers(therefore, the best time to use statement is a frequent parameter--the value of the parameter item rather than the parameter--the SQL statement at the time of the change)。

However, in addition to buffering, there is at least one better reason why we prefer to use PreparedStatement objects in enterprise applications, which is security. The arguments passed to the PreparedStatement object can be coerced into type conversion, allowing the developer to ensure that the data is inserted or queried to match the underlying database format.

When dealing with data coming from users on a public Web site, the issue of security becomes extremely important. String arguments passed to PreparedStatement are automatically ignored by the drive. In the simplest case, this means that when your program tries to insert the string "D ' Angelo" into VARCHAR2, the statement will not recognize the first "," resulting in a tragic failure. It is rarely necessary to create your own string-ignoring code.

In a Web environment, malicious users exploit applications that are not well-designed to handle strings correctly. In particular, on public Web sites, all user input should not be passed to the SQL statement without first processing through the PreparedStatement object. In addition, the SQL statement should not be displayed where the user has the opportunity to modify the SQL statement, such as the hidden area of the HTML or a query string.
When executing SQL commands, we have two choices: you can use the PreparedStatement object, or you can use the statement object. No matter how many times the same SQL command is used, PreparedStatement only parses and compiles it once. When a statement object is used, it is parsed and compiled each time an SQL command is executed.


First:

Preparestatement Initializes SQL first, submits the SQL to the database for preprocessing, and uses multiple times to improve efficiency.
Createstatement does not initialize, no preprocessing, no time is starting from 0 to execute SQL

Second:

Preparestatement can replace variables
Can be included in the SQL statement, you can use Ps=conn.preparestatement ("select * from Cust where id=?");
int sid=1001;
Ps.setint (1, SID);
rs = Ps.executequery ();
You can replace them with variables.
And statement can only use int sid=1001;
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery ("select * from Cust where id=" +sid);
To achieve.

Third:

Preparestatement Initializes SQL first, submits the SQL to the database for preprocessing, and uses multiple times to improve efficiency.
Createstatement does not initialize, no preprocessing, no time is starting from 0 to execute SQL

How the PreparedStatement greatly improves performance