Menu
What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause?
324
By default, most JDBC drivers use a fetch size of 10. , so if you are reading 1000 objects, increasing the fetch size to 256 can significantly reduce the time required to fetch the query's results.
jdbc. fetch_size a non-zero value determines the JDBC fetch size, which in turn calls java.
The setFetchSize(int) method defines the number of rows that will be read from the database when the ResultSet needs more rows. setFetchSize(int) affects how the database returns the ResultSet data. Whereas, setMaxRows(int) method of the ResultSet specifies how many rows a ResultSet can contain at a time.
fetch_size sets the statement's fetch size within the JDBC driver, that is the number of rows fetched when there is more than a one row result on select statements.
The SQL LIMIT will limit your SQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results.
The fetch size is the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query ResultSet with next(). For example, you set the query fetch size to 100. When you retrieve the first row, the JDBC driver retrieves the first 100 rows (or all of them if fewer than 100 rows satisfy the query). When you retrieve the second row, the JDBC driver merely returns the row from local memory - it doesn't have to retrieve that row from the database. This feature improves performance by reducing the number of calls (which are frequently network transmissions) to the database.
So, even if setting the fetch size is translated by JDBC into a SQL LIMIT clause, the big difference with forcing a SQL query with LIMIT is that with JDBC, you're actually still able to browse all the results.
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
