CODEWITHSUNDEEP
javasqljdbc
kal
kal

Reputation: 29353

What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause?

What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause?

Upvotes: 14

Views: 8777

Answers (2)

Smichovan
Smichovan

Reputation: 31

SQL LIMIT applies first, in the moment when SQL server is building a resultset as the answer for the query. It can have (and usually has) influence on query plan used and consequently also on SQL server response time. The resultset contains only rows that correnspond the limit - you cannot fetch more.

Fetch size comes afterwards when content of the resultset is transfered to client. For details see Pascals answer.

Upvotes: 0

Pascal Thivent
Pascal Thivent

Reputation: 570315

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.

Upvotes: 14

Related Questions