iWiz ShareBase

IT Specialist 윤태현의 iWiz ShareBase는 IT뿐 아니라 각종 잡다한 지식들을 함께 나누는 지식공유 커뮤니티입니다.

iWiz,ShareBase,윤태현,Java,JSP,EJB,IT,정보기술,웹프로그래밍,PHP,ASP,DBMS,MySQL,서버,네트워크,server,network,WAS,웹애플리케이션,블로그,blog,웹서버,DB,오라클,oracle,mysql,JRun,웹로직,톰캣,tomcat,아파치,자동차,EF쏘나타,로또 6/45

갤러리 Pixelgrapher.com | 로또 6/45 번호생성 및 통계 데이터 | 전체기사보기 | 전체글 #1 | 전체글 #2 | 전체글 #3 | 전체글 #4 | 전체글 #5 | 전체글 #6 | 전체글 #7 | 전체글 #8 | 전체글 #9 | 전체글 #10 |
HOME iWiz
ShareBase
Remember 0523 & 0818
지식은 나눌수록 커집니다 - iWiz's ShareBase
웹프로그래밍(기타) PHP, ASP, Perl, CGI 등 각종 웹프로그래밍에 관한 자료들입니다.


  iWiz(2004-01-04 22:37:44, Hit : 6815, Vote : 26
 http://www.wz.pe.kr

Optimizing JDBC


Optimizing JDBC

This section provides some ideas for improving your coding practices when accessing databases.

Using database connection pooling

With connection pooling, database connections are borrowed and returned to a pool of reusable objects, rather than created and destroyed for each request. JRun has its own database connection pooling mechanism built in and it is enabled by default for each data source you add in the JMC.

If you do not use JRun data sources, you must implement your own custom connection pooling mechanism.

You can manipulate the JRun data sources using optional settings that are not exposed in the JMC. All of these settings are optional. The following table describes these settings:
Parameter
Description
Default
native-results
Set to true to use the cacheable, scrollable, updatable JRun ResultSet implementation.
Set to false to use the ResultSet returned by the underlying JDBC driver.
true
initial-connections
Set the number of connections that JRun creates when the pool is instantiated.
1
pool-statements
Set to true to pool PreparedStatements instead of recreating them with each invocation.
true
minimum-size
Set the minimum number of objects that JRun retains in the pool.
0
maximum-size
Set the maximum number of objects that JRun maintains in the pool.
2147483647
maximum-soft
Set to true to have the connection pool create new emergency objects if the maximum size of a pool is reached but requests are still waiting on objects. This temporarily increases the size of the pool, but the pool shrinks back down to an acceptable size automatically when the skimmer activates.
Set to false to force the requests to wait until an object is available.
For information about the skimmer, see the description of the skimmer-frequency setting.
true
connection-timeout
Set the length of time in seconds that JRun lets a connection remain dormant before destroying it.
1200
user-timeout
Set the length of time in seconds that a user must keep a connection before it is automatically returned to the pool.
20
skimmer-frequency
Set the length of time in seconds that the pool skimmer waits between reap cycles. During each reap cycle, the skimmer evaluates all connections (both checked in and checked out) to determine whether to automatically return them to the pool or to destroy them if they have timed out.
420
shrink-by
Set the maximum number of objects that can be removed from the pool in any one reap cycle.
JRun checks this value each time the pool is sized down by the skimmer. It prevents JRun's connection pooling mechanism from backing off the pool too quickly at peak times.
5
debugging
Set to true to enable verbose logging information.
false
cache-enabled
Set to true to enable the query/ResultSet cache.
false
cache-size
Set the maximum number of query/ResultSet pairs that the cache can contain.
5
cache-refresh-interval
Set the interval, in seconds, between the cache's reloading of its ResultSets from the database.
30
remove-on-exceptions
Set to true to remove a Connection from the pool if there is a SQLException.
false

To change connection pool settings, edit the properties of the appropriate data source in the jrun-resources.xml file. For example, the following setting overrides the default skimmer-frequency value of 420 and replaces it with 100:

<datasource>
...
 <skimmer-frequency>100</skimmer-frequency>
...
</datasource>

Using prepared statements

Whenever you query a database, JRun compiles the query and sends it to the database through the database connection. For frequently used queries, you can use PreparedStatements. PreparedStatements are precompiled and stored on the server. They can contain any number of variables, indicated with question marks, which are inserted into the query at runtime.

The following code example shows how to build a PreparedStatement with three variables:

String sqlstmt = "INSERT INTO SESSIONS VALUES(?,?,?)";
try {
 InitialContext ctx = new InitialContext();
 DataSource ds = (DataSource) ctx.lookup(dsName);
 conn = ds.getConnection();
 ps = conn.prepareStatement(sqlstmt);
 ps.setString(1,sid);
 ps.setLong(2,time);
 ps.setInt(3,1);
 rs = ps.executeQuery();
} catch (SQLException sqle) {
...
}
...

For more information on using the PreparedStatement object, see the Java 2 API documentation.

Closing connections, statements and resultsets

After you use Connection, Statement, and ResultSet objects, close them to free up the resources that they claimed, which returns unused connections to the pool. Add a finally block, as shown in the following example:

...
} finally {
 try {
  ps.close();
  rs.close();
 } catch (Exception e) {
  e.printStackTrace();
 }
}

Limiting fetches and number of rows

The ResultSet and Statement objects expose a number of methods that are useful for tweaking database operations, including the following:

  • Statement.setMaxRows
  • setFetchSize
  • setFetchDirection

Developers often confuse the setFetchSize and setMaxRows methods. You can invoke them on either the ResultSet or Statement objects. To understand the difference between these methods, first you must understand what happens when a database returns the result of a query. The following sections describe querying a database, and the differences between the setFetchSize and setMaxRows methods.

Database query overview

When you submit a query to the database, the database processes the query and stores the resulting data in its cache. The ResultSet object holds references to the records in the database's cache, the number of which being equal to the fetch size. In other words, the ResultSet object is only an open connection to a database, and does not contain the data from the returned rows.

The JDBC driver, which provides the connection between the JRun server and the database, gets some of the rows from the database cache and returns them to the client-side (that is, the JRun server is the database's client) for processing. The driver gets the number of rows equal to getFetchSize.

When the client finishes processing the rows provided by the drivers, the JDBC driver prefetches the next getFetchSize rows, and so on.

Using setMaxRows

To limit the number of rows that the database returns from the query, use setMaxRows, as shown in the following example:

Statement stmt = conn.createStatement();
stmt.setMaxRows(10);
ResultSet rs = stmt.executeQuery("SELECT * from SESSIONS");

To limit the number of rows the database returns, you can also use the LIMIT command in your SQL statement. The following example shows a ResultSet being limited to 100 rows:

rs = ps.executeQuery("SELECT * FROM tablename LIMIT 100");

Using setFetchSize

To set the number of rows that the JDBC driver gets from the database cache and returns to JRun, use setFetchSize. Set it to lower numbers if the data requires a great deal of processing.

Note:   The setFetchSize method is only a hint to the database on how to fetch data. Not all database drivers support this method.

When determining an appropriate setFetchSize, consider the user interaction with the front end and how users will navigate the results of their queries. For example, if the user interface lets users interact with 10 rows at a time, set your fetch size to 10.

Using setFetchDirection

Use the setFetchDirection method if you are processing ResultSets and want to return the last rows first. This can speed up processing time, especially when working with a large amount of data.

For example, if you have a ResultSet of transactions that is sorted by date, you might want the most recent transactions in the first fetch.

Note:   The setFetchDirection method is only a hint to the database on how to fetch data. Not all database drivers support this method.

The following code shows an example of reversing the fetch direction:

ResultSet rs = null;
Connection conn = null;
String sql = "SELECT * FROM employee";
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = stmt.executeQuery(sql);

Testing the JDBC drivers

Not all JDBC drivers are alike. Test different drivers with different databases under a variety of loads to see which combination works best with your web application.

Use the JDBC-to-ODBC bridge only if absolutely necessary.

For more information on JDBC drivers, see the Sun website at http://industry.java.sun.com/products/jdbc/drivers

Caching static data

The fastest way to access a database is to not access it at all. Where possible, you should try to cache static data rather than obtain it dynamically through calls to a database. Limit access to the database to a single query during initialization in the init and jspInit methods of servlets and JSPs. Then cache the results and allow the remaining requests to access that cached data. For more information, see the following sections:

"Caching static data in the init method"

"Caching static data in jspInit"

* iWiz님에 의해서 게시물 이동되었습니다 (2010-02-03 16:57)



62   mod_throttle 모듈을 이용한 사용자 트래픽 제어  iWiz 2006/06/22 9111 0
61   Tomcat-Apache using JK2 connector  iWiz 2004/03/21 8272 41
60   RedHat 9.0에서의 JRun JSP 컴파일러의 문제점  iWiz 2004/01/04 5594 50
59   RedHat 9.0에서의 JRun-Apache 커넥터의 문제점  iWiz 2004/01/04 5219 48
58   JRun 4.0의 튜닝 관련 옵션  iWiz 2004/01/04 5988 68
57   JRun 4.0의 Activity 모니터링 방법  iWiz 2004/01/04 4957 57
56   JRun4.0: DataSource 커넥션풀 관련 옵션 [4]  iWiz 2004/01/04 6703 46
55   JRun에서 JSP 컴파일시 java 파일 생성하기  iWiz 2004/01/04 8114 63
54   JRun의 실제 서비스 운영시 고려사항  iWiz 2004/01/04 6436 44
53   수정된 인터넷 익스플로러에서 상호작용 ActiveX 컨트롤 활성화 가이드  iWiz 2006/03/03 8517 4
52   HTML 특수기호 엔터티(Entity) 테이블 [2]  iWiz 2006/03/03 14241 2
51   웹사이트의 새로운 혁명 Ajax [13]  iWiz 2005/11/22 5907 6
50   MSN 메신저 친구 자동등록 스크립트  iWiz 2004/10/12 6231 35
49   JavaScript MD5 해쉬 생성 함수  iWiz 2004/01/07 9156 35
48   JavaScript로 만든 진법변환 및 보수계산기 [4]  iWiz 2004/01/04 161318 51

1 [2][3][4][5]
 

Copyright 1999-2024 Zeroboard / skin by zero
iWiz ShareBase, ⓒCopyleft by iWiz.  For more information contact .
본 웹사이트에 게시된 이메일 주소가 전자우편 수집 프로그램이나 그 밖의 기술적 장치를 이용하여 무단으로 수집되는 것을 거부하며, 이를 위반시에는 정보통신망법에 의해 형사처벌됨을 유념하시기 바랍니다. [게시일 2004. 1. 31]