Using MySQL JDBC setfetchsize correctly

MYSQL JDBC Fast Query response method, the implementation of the fast return mechanism

has been very tangled, Oracle's fast return mechanism, although the result set a lot, but it can quickly display the first result, although through the MySQL client can do, but not through the JDBC.

Today took 1 hours, finally fix this problem, I hope that the vast number of Java friends in the processing of the database has a reference.

Reason:

With the-q parameter, the command-line client can respond very quickly to a query.
For example, a select * from T1 with a result set of tens of millions of, the full result set takes 20 seconds, and the first line is displayed with the-Q parameter in less than 1 seconds.
But the query through JDBC, but can not achieve the effect as above, no matter how to adjust the URL parameters, also not.

Process:
View the explanation of the-Q parameter, as follows:
If you had problems due to insufficient memory for large result sets,
Use the--quick option. This forces MySQL to retrieve results
From the server a row at a time rather than retrieving the entire result set
and buffering it in memory before displaying it. This is do by returning
The result set using the Mysql_use_result () C API function in the Client/server
Library rather than Mysql_store_result ().

Visible, enabling quick response.

Look at the Mysql_use_result () function, this is the C API, if developed through C, you can use this function.

So what about Java?

Find out about functions within the standard JDBC specification, without any gain. Setfetchsize () appears to be effective, and there is no performance improvement in the actual test.

Search for JDBC Mysql_use_result, with an unexpected harvest.

The following is found in the MySQL Jdbc,com.mysql.jdbc.statement interface:
Abstract public void Disablestreamingresults () throws SQLException

Resets this statements fetch size and result set type to the values they
Had before Enablestreamingresults () was called.

Abstract public void Enablestreamingresults () throws SQLException

Workaround for containers this ' check ' for sane values of statement.setfetchsize ()
So, applications can use the Java variant of Libmysql ' s Mysql_use_result () behavior.


The original MySQL provided a quick response to its own implementation. Adjust the test code

stmt = (com.mysql.jdbc.Statement) con.createstatement ();
Stmt.setfetchsize (1);
Read by row
Turn on the flow mode return mechanism
Stmt.enablestreamingresults ();

The effect I was expecting came up. The first line of data is quickly realistic and takes less than 1 seconds.

Conclusion:
MySQL provides unique functionality in its own JDBC driver to enable fast response of queries,

In particular, the result set is very large or long, and the user is particularly effective when they want to see the first result as soon as possible.

from:http://blog.csdn.net/java2000_net/article/details/6869752

Correct use of MySQL jdbc Setfetchsize () method to resolve JDBC processing large result set Java.lang.OutOfMemoryError:Java heap space

Yesterday in the project you need to export the query results of the log.

The implementation of the log export function is like this, enter the query criteria, and then export the query results. Because of the large amount of log data. Many times, there are hundreds of millions of records.

The previous solution was to query multiple times, and then limit the number of bars per query using limit. and then export. As a result, efficiency is relatively inefficient.

So can we just make a single query and pour out all the results? So I used the query once, not using the limit paging. As a result, the Java.lang.OutOfMemoryError:Java heap space problem occurs.

It appears that the DB server will send the result set of the query to the Java side in memory all at once. The Oom problem occurs because the result set is large.

The first thing I think about is the cursor function. So is it possible to use a cursor and take it slowly from the server side? Surf the internet for a bit, we all say that MySQL does not support cursor function and so on.

Then I went to see the JDBC code. Found the Setfetchsize () method, the result is set, but it does not take effect, or there is an oom problem.

My settings are as follows

[Java]View Plaincopy

1. Ps=conn.con.preparestatement ("SELECT * from bigTable");
2. Ps.setfetchsize (1000);

Later, in MySQL, the boss saw this method:

[Java]View Plaincopy

1. PS = (preparedstatement) con.preparestatement ("SELECT * from BigTable",
2. Resultset.type_forward_only, resultset.concur_read_only);
3. Ps.setfetchsize (Integer.min_value);
4. Ps.setfetchdirection (Resultset.fetch_reverse);

The explanation is that MySQL JDBC default client data is received in the following way:

The default is to remove all data from the server at once in client memory, and the fetch size parameter does not work, and the JVM OOM may occur when a large amount of SQL returns data.

For a SQL to read a large amount of data from the server, no JVM OOM occurs, you can use one of the following methods:

1, when the statement set the following properties, the use of the stream data receive mode, each time only from the server to receive some data, until all data processing is complete, the JVM OOM will not occur.

Setresultsettype (resultset.type_forward_only);

Setfetchsize (Integer.min_value);

2, Call statement Enablestreamingresults method, in fact Enablestreamingresults method internal encapsulation is the 1th way.

3, set the connection properties Usecursorfetch=true (version 5.0 driver start support), statement to Type_forward_only Open, and then set the fetch size parameter, the server-side cursor, each time from the server to take Fetch_ Size bar data.

After setting up, sure enough to solve my problem.

Attached code:

[Java]View Plaincopy

2. Package com.seven.dbTools.DBTools;
6. Import java.sql.Connection;
8. Import Java.sql.DriverManager;
10. Import java.sql.PreparedStatement;
12. Import Java.sql.ResultSet;
14. Import java.sql.SQLException;
16. Import java.sql.Statement;
18. Import java.util.ArrayList;
22. public class Jdbchandlemysqlbigresultset {
26. public static long ImportData (String sql) {
28. String url = "Jdbc:mysql://ipaddress:3306/test?user=username&password=password";
30. try {
32. Class.forName ("Com.mysql.jdbc.Driver");
34. } catch (ClassNotFoundException E1) {
36. E1.printstacktrace ();
38. }
40. Long Allstart = System.currenttimemillis ();
42. Long Count = 0;
46. Connection con = null;
48. PreparedStatement PS = null;
50. Statement st = null;
52. ResultSet rs = null;
54. try {
56. con = drivermanager.getconnection (URL);
60. PS = (preparedstatement) con.preparestatement (Sql,resultset.type_forward_only,
62. RESULTSET.CONCUR_READ_ONLY);
66. Ps.setfetchsize (Integer.min_value);
70. Ps.setfetchdirection (Resultset.fetch_reverse);
74. rs = Ps.executequery ();
80. while (Rs.next ()) {
84. Business logic is handled here
86. count++;
88. if (count%600000==0) {
90. System.out.println ("Write to section" + (count/600000) + "Files!) ");
92. Long end = System.currenttimemillis ();
94. }
98. }
100. System.out.println ("+count+" row for retrieving data volume!) ");
102. } catch (SQLException e) {
104. E.printstacktrace ();
106. } finally {
108. try {
110. if (rs!=null) {
112. Rs.close ();
114. }
116. } catch (SQLException e) {
118. E.printstacktrace ();
120. }
122. try {
124. if (ps!=null) {
126. Ps.close ();
128. }
130. } catch (SQLException e) {
132. E.printstacktrace ();
134. }
136. try {
138. if (con!=null) {
140. Con.close ();
142. }
144. } catch (SQLException e) {
146. E.printstacktrace ();
148. }
150. }
152. return count;
156. }
160. public static void Main (string[] args) throws Interruptedexception {
164. String sql = "SELECT * from test.bigtable";
166. ImportData (SQL);
170. }
174. }

Using MySQL JDBC setfetchsize correctly