JDBC read data Optimization-fetch size 01/16 Update SLTechnology News&Howtos

JDBC read data Optimization-fetch size

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Recently, due to business requirements, the data from an old table structure needs to be extracted and imported into a new table structure according to the rules. The developer wrote a tool to implement the transformation of the old and new structure.

The implementation logic is simple, that is to use jdbc to read the data from table A, do some processing, and then store it in the new table B. it is found that the operation of reading the old table is very slow and can not meet the requirements.

Sample code for reading data

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))

The SQL statement reads 10000 records, of which

Interval1=160ms

Interval2=29252ms

The time to perform the SQL retrieval of executeQuery () is 160ms.

The time taken to execute 10000 rs.next and rs.getString (1) is about 30 seconds, with an average of 3 milliseconds per record.

How can we improve the efficiency of reading?

The above reads 10000 records, and each rs.next takes only 3 milliseconds, but because it takes 10000 times, it only takes 30 seconds. We can guess whether it is possible that every execution of rs.next needs to interact with the database, because if it is only a string operation, it should not be this order of magnitude.

Take a look at the description of the official documentation. "Database JDBC Developer's Guide" has a section that introduces Fetch Size.

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows ata 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.

By default, JDBC extracts 10 rows of records from the cursor each time it performs a search, and 10 is the default row fetch size value. By setting row fetch size, you can change the total number of rows extracted each time you interact with the database. It is important to note that you need to set fetch size before getting the retrieval result set, otherwise it will be invalid.

You can set it using the following methods

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

To put it simply, Fetch is equivalent to read cache, the default FetchSize value is 10, read 10000 records, one database interaction, that is, rs.next operation, ResultSet will get 10 records from the database server at once, the next time rs.next is executed, it will be read directly in memory, no need to interact with the database, but a total of 1000 interactions are required. If you use setFetchSize to set FetchSize to 10000, only one database interaction is needed. Local cache 10000 records, each execution of rs.next, only memory operations, there will be no database network consumption, the efficiency will be higher. It is important to note, however, that the higher the Fetch Size value, the higher the memory footprint, so avoid OOM errors.

Option 1:

Rs = ps.executeQuery ()

Rs.setFetchSize (10000)

That is, after executing ps.executeQuery (), set a value of 10000 for rs, and the statistics are as follows

The time to perform the SQL search of executeQuery () is 174ms.

It takes about 190ms to execute 10000 rs.next and rs.getString (1).

Compared with the previous 10000 rs.next execution, it took 30 seconds, an increase of nearly 150 times.

Option 2:

Ps = conn.prepareStatement (sql)

Ps.setFetchSize (10000)

That is, after executing conn.prepareStatement (sql) and before executing ps.executeQuery (), set the value of rs to 10000 range. The statistics are as follows

The time to perform the SQL retrieval of executeQuery () is 267ms.

It takes about 87 milliseconds to execute 10000 rs.next and rs.getString (1).

Compared to scenario 2, the total time is almost the same, but there are some differences between SQL execution and rs.next traversal.

For option 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.

After the query is executed, setting setFetchSize on the result set affects any subsequent database interaction process to get more rows of records, as well as subsequent fetch extraction.

For option 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.

Before executing the query, set setFetchSize, which means that each interaction with the database gets the number of rows recorded.

To sum up, it is recommended that you set this value before implementing SQL to achieve the highest efficiency.

For PrepareStatement, ResultSet, and Statement, there is this method, one difference is that the default value setting (0), from the code to use getFetchSize (), the value is 10, I do not know if I misunderstood, or has other meaning? Welcome your comments.

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 ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

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