Preparedstatement
How to greatly improve performance
By BILLY Newport
This article describes how to use prepared statements correctly. Why does it make your application program run faster and Database Operations faster.
Why?
Prepared statements
Very important? How can I use it correctly?
The database has a very hard job. They accept SQL queries from many concurrent clients and execute the queries as quickly as possible and return results. Processing statements is an expensive operation, but now with methods like prepared statements, this overhead can be minimized. But this optimization needs to be done by developers. This article will show you how to use prepared statements correctly to optimize database operations.
How does a database execute
Statement
?
Obviously, I won't write a lot of details here. We only focus on the most important part. When a database receives a statement, the database engine first parses the statement and then checks whether it has a syntax error. Once the statement is correctly parsed, the database selects the optimal way to execute the statement. Unfortunately, this computing overhead is very expensive. The database first checks whether there are related indexes to help with this, whether or not all rows in a table are read. The database collects statistics on the data and then selects the optimal path. After a query scheme is created, the database engine executes the scheme. The generation of an access plan occupies a considerable amount of CPU. Ideally, when we send a statement multiple times to the database, the database should reuse the statement access scheme. If the scheme has been generated, this will reduce the CPU usage.
Statement caches
The database already has similar functions. They usually cache statement using the following method. Use statement as the key and save the ACCESS scheme to the cache corresponding to statement. In this way, the database engine can reuse the ACCESS scheme in statements that have been executed. For example, if we send a statement containing select a, B from t where c = 2 to the database, then the ACCESS scheme will be cached first. When we send the same statement again, the database will reuse the previously used access scheme, which reduces the CPU overhead. Note that the entire statement is used as the key. That is to say, if we send a statement containing select a, B from t where c = 3, there will be no corresponding access scheme in the cache. This is because "c = 3" is different from "c = 2" that has been cached. So, for example: for (INT I = 0; I <1000; I ++) {preparedstatement PS = Conn. preparestatement ("select a, B from t where c =" + I); resultset rs = ps.exe cutequery (); RS. close (); PS. close ();} Here the cache will not be used, because each iteration will send a statement containing different SQL statements to the database. Each iteration generates a new access scheme. Now let's 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.exe cutequery (); RS. close (); PS. close ();} achieves better efficiency. The statement is sent to the database with a parameter "?" SQL statement. In this way, the same statement will be sent to the database for each iteration, but the parameter "c = ?" Different. This method allows the database to reuse the statement access scheme, which improves the efficiency. This allows your applications to be faster and use less CPU, so that the database server can serve more people.
Preparedstatement
And
J2EE
Server
When we use the J2EE server, the current situation will become more complex. Usually, a perpared statement is associated with a separate database connection. When the database connection is closed, the prepared statement is also discarded. Generally, a fat client gets a database connection and keeps it until it exits. It creates all parepared statements in the "eagerly" or "lazily" mode. The "Hunger" method creates everything when the application is started. The "lazy" method means that it is created only when used. The "Hunger" method will delay the startup of the application, but it will run quite well once started. The "lazy" method enables the application to start very quickly (but won't do any preparation work). You can create a prepared statement when you need to use it. In this way, the performance is very unstable during the creation of all statement, but once all statement is created, it works as well as a "Hungry" application. Please select the best method based on your needs. Is it quick start? Or consistent performance. The problem with the J2EE application is that it does not work like this, and the connection will only be maintained during the request. This means that prepared statement must be created for each request. This is far from the performance of the fat client that keeps executing the prepared statement. J2EE vendors have noticed this problem and provide a connection pool (connectionpool) to avoid this problem. When the J2EE server provides a connection to your application, it does not actually provide you with a real database connection. You just get a wrapper ). You can check the name of the connected class you obtained 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 connection pool manager of the application server. All JDBC resultsets, statements, callablestatements, and preparedstatements will be packaged and returned to the application in the form of a "proxy object. When you close the connection, these objects are marked as invalid and recycled by the garbage collector. Normally, if you close a database connection, the connection will be closed by the JDBC driver. However, when the J2EE server closes, the database connection will be 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 the real connection. When we execute a connection method, the proxy will forward the operation to the real connection. However, when we close a connection, the connection will not be closed, but will be sent back to the connection pool and can be used by other requests. A Prepared prepared statement will also be reused.
J2EE preparedstatement Cache
The connection pool manager of the J2EE server has been used for caching. The J2EE server maintains the prepared statement list prepared for each connection in the connection pool. When preparedstatement is called on a connection, the application server checks whether the statement has been prepared. If yes, the preparedstatement will be returned to the application. If no, the call will be transferred to the JDBC driver and the newly generated statement object will be 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, we can use parameterized query statements to find the statement we have used in the cache. Most application servers allow you to adjust the size of the prepared statements cache.
Summary
We should definitely use the prepared statement that contains the parameterized query statement. In this way, the database will reuse the prepared access solution. The cache applies to the entire database. Therefore, if you want all applications to use the same parameterized SQL statement, other applications can reuse the 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 ing, Entity Bean, JDBC, etc ). Second, correct use of prepared statement is also the key to taking advantage of prepared statement cache. Because the application can reuse the prepared statement, the number of times the JDBC driver is called is reduced, thus improving the application performance. In this way, you can compare the efficiency with that of the fat client, but you do not need to maintain a connection. With parameterized prepared statement, your application will have better performance.
