5

I have MySql table with 16 millions records, because of some migration work I'm reading whole Mysql table.

The following code is used for streaming large ResultSet in MySql

        statement = connection.createStatement(
                java.sql.ResultSet.TYPE_FORWARD_ONLY,
                java.sql.ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(Integer.MIN_VALUE);

but this is streaming one result at a time , does it mean we are hitting MySql server for each row

while using streaming can we set something like this statement.setFetchSize(1000);

I want to reduce number of round trips to server while streaming large ResultSet

Rams
  • 2,141
  • 5
  • 33
  • 59
  • 1
    read this http://stackoverflow.com/questions/2447324/streaming-large-result-sets-with-mysql?rq=1 more similar – Santhosh Jul 10 '14 at 12:08

2 Answers2

4

I will assume that you are using the official MySQL provided JDBC driver Connector/J.

You are explicitly telling JDBC (and MySQL) to stream the results row-by-row with statement.setFetchSize(Integer.MIN_VALUE);

From MYSQL Docs:

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
          java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

Any value other than Integer.MIN_VALUE for the fetch size is ignored by MySQL, and the standard behavior applies. The entire result set will be fetched by the JDBC driver.

Either don't use setFetchSize(), so the JDBC driver will use the default value (0), or set the value to 0 explicitly. Using the value of 0 will also ensure that JDBC doesn't use MySQL cursors, which may occur depending on your MySQL and Connector/J versions and configuration.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • 2
    that means there is no way to load 1000 or so rows at a time while doing streaming am I right?? – Rams Jul 10 '14 at 13:33
  • You're going to have to be specific what you mean when you say "streaming". Please show some code. – Marcus Adams Jul 10 '14 at 17:09
  • 3
    I'm asking instead of doing stream resultset row by row can we stream 1000 rows at a time – Rams Jul 11 '14 at 05:58
3

To fetch with a fixed number of records at a time (e.g. batches of 1000) instead of record by record streaming with the MySQL JDBC driver, you need to either:

  1. Set useCursorFetch to true and defaultFetchSize to desired batch size in your JDBC URL e.g.

    jdbc:mysql://localhost/?useCursorFetch=true&defaultFetchSize=1000

  2. As per the MySQL driver documentation, set useCursorFetch to true in the JDBC URL and then call setFetchSize(1000) after creating the statement. See code below:

    conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
stmt = conn.createStatement();
stmt.setFetchSize(1000);
rs = stmt.executeQuery("SELECT * FROM your_table_here");

See official documentation for more info: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-implementation-notes.html

grange74
  • 59
  • 2