Performance tips for the data tierWord文档格式.docx

1、Using Database Metadata MethodsBecause database metadata methods that generate ResultSet objects are slow compared to other JDBC methods, their frequent use can impair system performance. The guidelines in this section will help you optimize system performance when selecting and using database metad

2、ata.Minimizing the Use of Database Metadata Methods Compared to other JDBC methods, database metadata methods that generate ResultSet objects are relatively slow. Applications should cache information returned from result sets that generate database metadata methods so that multiple executions are n

3、ot needed.Although almost no JDBC application can be written without database metadata methods, you can improve system performance by minimizing their use. To return all result column information mandated by the JDBC specification, a JDBC driver may have to perform complex queries or multiple querie

4、s to return the necessary result set for a single call to a database metadata method. These particular elements of the SQL language are performance expensive. Applications should cache information from database metadata methods. For example, call getTypeInfo once in the application and cache away th

5、e elements of the result set that your application depends on. It is unlikely that any application uses all elements of the result set generated by a database metadata method, so the cache of information should not be difficult to maintain.Avoiding Search PatternsUsing null arguments or search patte

6、rns in database metadata methods results in generating time-consuming queries. In addition, network traffic potentially increases due to unwanted results. Always supply as many non-null arguments to result sets that generate database metadata methods as possible.Because database metadata methods are

7、 slow, applications should invoke them as efficiently as possible. Many applications pass the fewest non-null arguments necessary for the function to return success.for example:ResultSet WSrs = WSc.getTables （null, null, WSTable, null）;should be:ResultSet WSrs = WSc.getTables （cat1, johng,TABLE）;In

8、the first getTables（） call, the application probably wants to know if the table WSTable exists. Of course, a JDBC driver takes the call literally and interprets the request differently. A JDBC driver interprets the request as: return all tables, views, system tables, synonyms, temporary tables, or a

9、liases that exist in any database schema inside any database catalog that are named WSTable.The second call to getTables（） more accurately reflects what the application wants to know. A JDBC driver interprets this request as: return all tables that exist in the johng schema in the current catalog wh

10、ere the name is WSTable. Clearly, a JDBC driver can process the second request much more efficiently than it can process the first request.Sometimes, little information is known about the object for which you are requesting information. Any information that the application can send the driver when c

11、alling database metadata methods can result in improved performance and reliability. Using a Dummy Query to Determine Table CharacteristicsAvoid using getColumns（） to determine characteristics about a table. Instead, use a dummy query with getMetadata（）.Consider an application that allows the user t

12、o choose the columns that will be selected. Should the application use getColumns（） to return information about the columns to the user or instead prepare a dummy query and call getMetadata（）?Case 1: GetColumns MethodResultSet WSrc = WSc.getColumns （. UnknownTable .）;/ This call to getColumns（） will

13、 generate a query to/ the system catalogs. possibly a join/ which must be prepared, executed, and produce/ a result set. . .WSrc.next（）;string Cname = getString（4）;/ user must retrieve N rows from the server/ N = # result columns of UnknownTable/ result column information has now been obtainedCase 2

14、: 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）In

15、both cases, a query is sent to the server. But in Case 1, the query must be prepared and executed, the result description information must be formulated, and a result set of rows must be sent to the client. In Case 2, a simple query must be prepared and only result description information must be fo

16、rmulated. Clearly, Case 2 is the better performing model. To somewhat complicate this discussion, let us consider a DBMS server that does not natively support preparing a SQL statement. The performance of Case 1 does not change, but the performance of Case 2 increases slightly because the dummy quer

17、y must be evaluated instead of only prepared. Because the Where clause of the query always evaluates to FALSE, the query generates no result rows and should execute without accessing table data. For this situation, method 2 still outperforms method 1.In summary, always use result set metadata to ret

18、rieve table column information such as column names, column data types, and column precision and scale. Only use getColumns（） when the requested information cannot be obtained from result set metadata （i.e. table column default values）.Look out for next months performance tip, on Retrieving Only Req

19、uired Data. Using Database MetaData Methods Appropriately Retrieving only Required Data Selecting Functions that Optimize Performance Managing Connections and Updates Designing and Running Benchmarks for Performance In the last performance tip, we discussed using database metadata appropriately. The

20、se general rules about retrieving data should help you solve some common JDBC system performance problems.Retrieving DataTo retrieve data efficiently, return only the data that you need, and choose the most efficient method of doing so. The guidelines in this section will help you to optimize system

21、 performance when retrieving data with JDBC applications.Retrieving Long DataUnless it is necessary, applications should not request long data because retrieving long data across a network is slow and resource-intensive. Most users dont want to see long data. If the user does want to process these r

22、esult items, then the application can query the database again, specifying only the long columns in the select list. This method allows the average user to retrieve the result set without having to pay a high performance penalty for network traffic.Although the best method is to exclude long data fr

23、om the select list, some applications do not formulate the select list before sending the query to the JDBC driver （that is, some applications send select * from .）. If the select list contains long data, most JDBC drivers must retrieve that data at fetch time, even if the application does not ask f

24、or the long data in the result set. When possible, the developer should attempt to implement a method that does not retrieve all columns of the table.For example, consider the following JDBC code:ResultSet rs = stmt.executeQuery （select * from Employees where SSID = 999-99-2222rs.next（）;string name

25、= rs.getString （4）;Remember that a JDBC driver is not intuitive. It has no idea what result columns an application might be trying to retrieve when the query is executed. A driver only knows that an application can request any of the result columns. When the JDBC driver processes the rs.next（） reque

26、st, it will most likely return at least one （if not more） result rows across the network from the database server. In this case, a result row will contain all the column values for each row including an employee picture if the Employees table happens to contain such a column. Limiting the select lis

27、t to contain only the name column results in decreased network traffic and a faster performing query at runtime.Additionally, although the getClob（） and getBlob（） methods allow the application to control how long data is retrieved in the application, the developer must realize that in many cases the

28、 JDBC driver emulates these methods due to the lack of true LOB locator support in the DBMS. In such cases, the driver must retrieve all of the long data across the network before exposing the getClob（） and getBlob（） methods.Reducing the Size of Data RetrievedSometimes long data must be retrieved. When this is the case, remember that most users might not want to see 100 KB （or more） of text on the screen. To reduce network traffic and improve performance, you can reduce the size of any data being retrieved to some manag