JDBC読み出しデータ最適化-fetch size
最近、ビジネス上のニーズのため、古いテーブル構造のデータを抽出する必要があります.ルールに基づいて、新しいテーブル構造を導入し、新しい古い構造を実現するためのtransformationを開発するためのツールを書きました.
実現ロジックは簡単で、jdbcを使ってA表からデータを読み出して、いくつかの処理をして、更に新しい表Bの中に保存して、古い表を読み取る操作を発見して、とても遅くて、要求を満たすことができません.
データのサンプルコードを読み出し、
conn = getConnection(); long start = System.currentTimeMillis(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(); long mid_end = System.currentTimeMillis(); while (rs.next()) { list.add(rs.getString(1)); } long end = System.currentTimeMillis(); rs.close(); System.out.println("Interval1="+ (mid_end - start)); System.out.println("Interval2="+ (end - mid_end));
SQL文は、10000個のレコードを読み込みます.
Interval1=160ms Interval2=29252ms
executeQuery()というSQL検索を実行する時間は160ミリ秒です.
rs.nextおよびrs.getString(1)を10000回実行するのに約30秒かかり、平均1本が3ミリ秒を記録した.
読み取りの効率を向上させるにはどうすればいいですか?
上に10000個のレコードが読み込まれ、rs.nextごとに3ミリ秒しかかかりませんが、10000回が必要なので30秒しかかかりません.rs.nextの実行ごとにデータベースとインタラクティブになる可能性があるかどうかを推測することができます.文字列操作のみであれば、このレベルであるべきではありません.
公式文書の説明を見ると、『Database JDBC Developer's Guide』にはFetch Sizeが紹介されています.
By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value. Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object. Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it. Changes made to the fetch size of a statement object after a result set is produced will have no affect on that result set.
JDBCのデフォルトでは、検索を実行するたびにカーソルから10行のレコードが抽出されます.10はデフォルトのrow fetch size値です.row fetch sizeを設定することで、データベースと対話するたびに抽出されるレコードの合計数を変更できます.検索結果セットを取得する前にfetch sizeを設定する必要があります.そうしないと無効です.
以下の方法で設定できます.
Setting the Fetch Size The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size: void setFetchSize(int rows) throws SQLException int getFetchSize() throws SQLException
簡単に言えば、Fetchはリードキャッシュに相当し、デフォルトのFetch Size値は10で、10000個のレコードを読み取り、1回のデータベースインタラクション、すなわちrs.nextの操作で、ResultSetは一度にデータベースサーバから10個のレコードを得て、次回rs.nextを実行すると、メモリ読み取りを直接使用して、データベースとインタラクションする必要はありませんが、合計1000回のインタラクションが必要です.setFetchSizeを使用してFetch Sizeを10000に設定すると、データベースのインタラクションを1回だけ必要とし、10000個のレコードをローカルにキャッシュし、rs.nextを実行するたびに、メモリ操作だけで、データベースネットワークの消費がなく、効率が高くなります.ただし、Fetch Sizeの値が高いほどメモリ消費量が高くなり、OOMエラーが発生しないように注意してください.
シナリオ1:
rs = ps.executeQuery(); rs.setFetchSize(10000);
すなわち、ps.executeQuery()を実行した後、rsに対して10000の値を設定し、統計は以下の通りである.
executeQuery()というSQL検索を実行する時間は174ミリ秒です.
rs.nextおよびrs.getString(1)を10000回実行する時間は約190ミリ秒である.
これまでrs.nextを10000回実行したのに比べて30秒で150倍近く向上した.
シナリオ2:
ps = conn.prepareStatement(sql); ps.setFetchSize(10000);
すなわちconn.prepareStatement(sql)を実行した後、ps.executeQuery()を実行する前にrsに対して10000の範囲を設定し、統計は以下の通りである.
executeQuery()というSQL検索を実行する時間は267ミリ秒です.
rs.nextおよびrs.getString(1)を10000回実行する時間は約87ミリ秒である.
シナリオ2と比較して、総使用時間はほぼ一致するが、SQL実行とrs.next遍歴の使用時間には、いくつかの違いがある.
シナリオ1に対して、
After you have run the query, you can call
クエリーを実行した後、結果セットにsetFetchSizeを設定すると、次のデータベース・インタラクション・プロシージャでより多くのレコード・ロー数が得られ、その後のfetch抽出に影響します.
シナリオ2に対して、
To set the fetch size for a query, call
クエリーを実行する前に、setFetchSizeを設定します.setFetchSizeは、データベースと相互作用するたびにレコードの行数が得られることを示します.
以上のように、SQLを実行する前に、この値を設定すると、最も効率が向上します.
PrepareStatement、ResultSet、Statementについて、いずれもこの方法がありますが、少し違いがあります.デフォルト値設定(0)です.コードからgetFetchSize()を使用して、得られた値はすべて10で、私が間違っているのか、それとも他の意味があるのか分かりません.ご指導を歓迎します.
PrepareStatement setFetchSize void setFetchSize(int rows) throws SQLException Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for Parameters: rows - the number of rows to fetch Throws: SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied. Since: 1.2 See Also: getFetchSize()
ResultSet setFetchSize void setFetchSize(int rows) throws SQLException Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this Parameters: rows - the number of rows to fetch Throws: SQLException - if a database access error occurs; this method is called on a closed result set or the condition rows >= 0 is not satisfied Since: 1.2 See Also: getFetchSize()
Statement setFetchSize void setFetchSize(int rows) throws SQLException Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for Parameters: rows - the number of rows to fetch Throws: SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied. Since: 1.2 See Also: getFetchSize()
まとめ:
1.Fetchはリードキャッシュに相当し、setFetchSizeを使用してFetch Sizeを10000に設定し、ローカルに10000個のレコードをキャッシュし、rs.nextを実行するたびに、メモリ操作だけで、データベースネットワークの消費がなく、効率が高くなる.ただし、Fetch Sizeの値が高いほどメモリ消費量が高くなり、OOMエラーが発生しないように注意してください.
2.SQL文を実行する前に、ps.executeQuery()という設定を推奨します.以前はsetFetchSize()関数を使用して設定していました.
実現ロジックは簡単で、jdbcを使ってA表からデータを読み出して、いくつかの処理をして、更に新しい表Bの中に保存して、古い表を読み取る操作を発見して、とても遅くて、要求を満たすことができません.
データのサンプルコードを読み出し、
conn = getConnection(); long start = System.currentTimeMillis(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(); long mid_end = System.currentTimeMillis(); while (rs.next()) { list.add(rs.getString(1)); } long end = System.currentTimeMillis(); rs.close(); System.out.println("Interval1="+ (mid_end - start)); System.out.println("Interval2="+ (end - mid_end));
SQL文は、10000個のレコードを読み込みます.
Interval1=160ms Interval2=29252ms
executeQuery()というSQL検索を実行する時間は160ミリ秒です.
rs.nextおよびrs.getString(1)を10000回実行するのに約30秒かかり、平均1本が3ミリ秒を記録した.
読み取りの効率を向上させるにはどうすればいいですか?
上に10000個のレコードが読み込まれ、rs.nextごとに3ミリ秒しかかかりませんが、10000回が必要なので30秒しかかかりません.rs.nextの実行ごとにデータベースとインタラクティブになる可能性があるかどうかを推測することができます.文字列操作のみであれば、このレベルであるべきではありません.
公式文書の説明を見ると、『Database JDBC Developer's Guide』にはFetch Sizeが紹介されています.
By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value. Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object. Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it. Changes made to the fetch size of a statement object after a result set is produced will have no affect on that result set.
JDBCのデフォルトでは、検索を実行するたびにカーソルから10行のレコードが抽出されます.10はデフォルトのrow fetch size値です.row fetch sizeを設定することで、データベースと対話するたびに抽出されるレコードの合計数を変更できます.検索結果セットを取得する前にfetch sizeを設定する必要があります.そうしないと無効です.
以下の方法で設定できます.
Setting the Fetch Size The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:
簡単に言えば、Fetchはリードキャッシュに相当し、デフォルトのFetch Size値は10で、10000個のレコードを読み取り、1回のデータベースインタラクション、すなわちrs.nextの操作で、ResultSetは一度にデータベースサーバから10個のレコードを得て、次回rs.nextを実行すると、メモリ読み取りを直接使用して、データベースとインタラクションする必要はありませんが、合計1000回のインタラクションが必要です.setFetchSizeを使用してFetch Sizeを10000に設定すると、データベースのインタラクションを1回だけ必要とし、10000個のレコードをローカルにキャッシュし、rs.nextを実行するたびに、メモリ操作だけで、データベースネットワークの消費がなく、効率が高くなります.ただし、Fetch Sizeの値が高いほどメモリ消費量が高くなり、OOMエラーが発生しないように注意してください.
シナリオ1:
rs = ps.executeQuery(); rs.setFetchSize(10000);
すなわち、ps.executeQuery()を実行した後、rsに対して10000の値を設定し、統計は以下の通りである.
executeQuery()というSQL検索を実行する時間は174ミリ秒です.
rs.nextおよびrs.getString(1)を10000回実行する時間は約190ミリ秒である.
これまでrs.nextを10000回実行したのに比べて30秒で150倍近く向上した.
シナリオ2:
ps = conn.prepareStatement(sql); ps.setFetchSize(10000);
すなわちconn.prepareStatement(sql)を実行した後、ps.executeQuery()を実行する前にrsに対して10000の範囲を設定し、統計は以下の通りである.
executeQuery()というSQL検索を実行する時間は267ミリ秒です.
rs.nextおよびrs.getString(1)を10000回実行する時間は約87ミリ秒である.
シナリオ2と比較して、総使用時間はほぼ一致するが、SQL実行とrs.next遍歴の使用時間には、いくつかの違いがある.
シナリオ1に対して、
After you have run the query, you can call
setFetchSize on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query, as well as affecting any later refetching of rows. クエリーを実行した後、結果セットにsetFetchSizeを設定すると、次のデータベース・インタラクション・プロシージャでより多くのレコード・ロー数が得られ、その後のfetch抽出に影響します.
シナリオ2に対して、
To set the fetch size for a query, call
setFetchSize on the statement object prior to running the query. If you set the fetch size to N, then N rows are fetched with each trip to the database. クエリーを実行する前に、setFetchSizeを設定します.setFetchSizeは、データベースと相互作用するたびにレコードの行数が得られることを示します.
以上のように、SQLを実行する前に、この値を設定すると、最も効率が向上します.
PrepareStatement、ResultSet、Statementについて、いずれもこの方法がありますが、少し違いがあります.デフォルト値設定(0)です.コードからgetFetchSize()を使用して、得られた値はすべて10で、私が間違っているのか、それとも他の意味があるのか分かりません.ご指導を歓迎します.
PrepareStatement
ResultSet objects generated by this Statement . If the value specified is zero, then the hint is ignored. The default value is zero. ResultSet
ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. The default value is set by the Statement object that created the result set. The fetch size may be changed at any time. Statement
ResultSet objects generated by this Statement . If the value specified is zero, then the hint is ignored. The default value is zero. まとめ:
1.Fetchはリードキャッシュに相当し、setFetchSizeを使用してFetch Sizeを10000に設定し、ローカルに10000個のレコードをキャッシュし、rs.nextを実行するたびに、メモリ操作だけで、データベースネットワークの消費がなく、効率が高くなる.ただし、Fetch Sizeの値が高いほどメモリ消費量が高くなり、OOMエラーが発生しないように注意してください.
2.SQL文を実行する前に、ps.executeQuery()という設定を推奨します.以前はsetFetchSize()関数を使用して設定していました.