In J2EE applications, we often access corporate resources through JDBC. But JDBC is not well used, will affect the performance of the system. This article refers to the article "Performance Tips for The Data Tier (JDBC)" in John Goodson, and I hope to help our development. This article describes the following four parts: l Appropriately use the metadata method for the database to retrieve the required data 1 to select the optimized performance L management connection and data update 1. Appropriately use the metadata method for the application to use the database. The metadata method is relatively slow due to the execution speed of the metadata method, so it is necessary to use a metammata method. Since the call metadata method generates a result set requires a large number of overhead, the result set generated by the metadata method should be cached instead of performing a query multiple times, this can provide the performance of JDBC. For example, in the application, you call GetTypeInfo once, you should save the result set and use it again. 1.2. Avoiding query mode gives metadata to provide NULL parameters or query mode will generate a consuming query. At the same time, since some unwanted data passed through network, the increase in network traffic is increased, and the performance of the entire system is reduced. Since the metadata method is slow, it provides non-NULL parameters and efficiently call it as much as possible. And our application often happens: ResultSet WSRS = WSC.GETTABLES (NULL, NULL, "WSTABLE", NULL); ResultSet WSRS = WSC.GETTABLES ("Cat1", "Johng", "Wstable" , "Table"); Obviously, in the first GetTables () call, the application may need to know if the Wstable table exists. Of course, the JDBC driver is different from the parsed request. JDBC is a resolution request: Returns all names, views, system tables, synonyms, zero tables called "wstable", or alias, or alias in any database directory. The second GetTables () call more accurately reflects what the application needs to know. JDBC parsing this request: Returns all the tables called "Wstable" exists in the current directory. Obviously, the second request of the JDBC driver is more efficient than processing the first request. Get metadata The more information provided by the method, the higher the accuracy and performance of the information you get. 1.3. Use the dumb query to determine the characteristics of the table to avoid using getColumns () to determine the characteristics of a table. Use getMedata () done query replacement Consider an application that allows the user to select the column.
What should I use with getColumns () to return the user column or prepare a dumb query and call getMetadata ()? Situation 1: getColumns method ResultSet WSRC = wsc.getColumns (... "unknownTable" ...); // this call to getColumns () Will Generate a query to // the system catalogs ... Possibly a join // Which Must be prepaared, and product // a result set.... .wsrc.next (); string cname = getString (4) ;.. .................................. of UnknownTable // result column information has now been obtained scenario 2: getMetadata method // prepare dummy queryPreparedStatement WSps = WSc.prepareStatement ( "SELECT * from UnknownTable WHERE 1 = 0"); // query is never executed on the server - only preparedResultSetMetaData WSsmd = WSps.getMetaData (); int numcols = WSrsmd.getColumnCount (); ... int ctype = WSrsmd.getColumnType (n) ... // result column information has now been obtained in both cases, the query is Send it to the server. However, in the situation 1, the query must be prepared and executed, and the description information must be simply expressed, and the result set must be sent to the client. In the case 2, a simple query must be prepared and there is only the result description information being simply described. Obviously, the situation 2 is a better performance mode. How many of this complicate this discussion, let's consider a database that does not support local preparing SQL statements. The performance of the situation 1 is not changed, but since the dumb query must be evaluated rather than just preparing, the performance of the situation is slightly increased. Since the WHERE clause calculation result of the query statement is always false, the query does not have the execution of the resulting row and the notgent table data. In this case, method 2 is still better than method 1. In short, always use the result of the column information, such as column name, column data type, and numerical range. When the requested information cannot be obtained from the result record set (eg, a table rod by default), only the getColumns () method is used. 2. Retrieve the required data 2.1. Search long data unless necessary, the performance is reduced due to the network resource tight. Usually most users do not need to see long data, if the user needs to see these data, the application will retrieve it. Such code in our code: SELECT * from
