# Using Streamingresult For Large Result Sets

__*useCurosrFetch*__
__*StreamingResult*__

+ By default, JDBC fetches query results in advance and saves them in client memory
+ Two recommended treatments
  + Set`FetchSize``Integer.MIN_VALUE` so that the client does not cache, and uses `StreamingResult`
  + Set the JDBC`useCursorFetch` flag to`true`, and set the statement flag `FetchSize`is a positive integer

```12
jdbc:mysql://<tidb_server>:<port>/<db_name>?~!@useCursorFetch=true@!~
...
preparedStatement.setFetchSize()
```

------------------------------------------------------------

When using JDBC for MySQL, if the query result set is too large and you use one query, java.lang.OutOfMemoryError: Java heap space problem may occur, because the DB server sends all the query result sets to the Java side at once and saves them in memory, causing OOM.

MySQL JDBC requires a single SQL to read a lot of data from the database without JVM OOM happening, one of the following method can be used:
1. When the following properties are set in the statement, the stream data is received. Only part of the data is received from the server at a time, until all the data is processed, no JVM OOM will occur.

  1.1 SetResultSetType (ResultSet.type_forward_only);
  1.2 setFetchSize (Integer.min_VALUE); 
    1.2.1. Call the statement's enableStreamingResults method. In fact, the first method is encapsulated inside the EnableStreamingResults method.

    1.2.2. Set the connection property UseCursorFetch=TRUE (supported by version 5.0 drivers), open the statement with type_forward_only, and then set the fetch size flag to indicate that the server-side cursor is used to fetch fetch_size data from the server each time.

Therefore, the OOM problem can be solved by using the following methods:

```
ps = (PreparedStatement) con.prepareStatement("select * from bigTable",
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
```

By default, the driver fetches all the results from the query at once. This may be inconvenient for large data sets, so the JDBC driver provides a method for setting up a ResultSet that extracts several rows from a database cursor. A small portion of the data row is buffered on the client side of the connection, and when exhausted, the next data row block is retrieved by repositioning the cursor.
SetFetchSize is primarily designed to reduce the number of network interactions. Accessing a ResultSet can incur significant overhead if it only fetches one row of data from the server at a time. SetFetchSize means that when rs.next is called, the ResultSet will get back how many rows of data from the server at once, so that the next time rs.next, it can directly fetch data from memory without network interaction, improving efficiency. This setting may be ignored by some JDBC drivers, and setting it too large can cause memory to rise.

In general, to improve execution efficiency, JDBC obtains query results in advance by default and saves them in client memory. However, in a scenario where a query return a very large result set, the client wants the database server to reduce the number of records return to the client at one time, and wait for the client to process a portion of the limited memory before going to the server for the next batch.
In JDBC, there are usually two ways to handle it:
(1) Set fetchSize to Integer.MIN_VALUE so that the client does not cache, and the client streams the execution result from the network connection by means of StreamingResult.
(2) To use Cursor Fetch, you first need to set FetchSize to a positive integer, and configure useCursorFetch = true in the JDBC URL.
Both methods are supported in TiDB, but the first method of setting fetchSize to Integer.MIN_VALUE is recommended, which is simpler to implement and more efficient than the second function.