What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause?
在JDBC中设置语句获取大小和使用LIMIT子句启动SQL查询有什么区别?
2 个解决方案
#1
13
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.
SQL限制将把SQL查询结果限制在指定范围内。您可以使用它来显示第一个X个结果，或者显示X - Y结果的范围。
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.
fetch大小是JDBC驱动程序一次从数据库中检索到的行数，您可以在接下来的查询结果集中滚动。例如，您将查询的大小设置为100。当检索第一行时，JDBC驱动程序检索前100行(如果少于100行满足查询，则检索所有行)。当您检索第二行时，JDBC驱动程序仅仅从本地内存返回行—它不必从数据库中检索该行。这个特性通过减少对数据库的调用(通常是网络传输)来提高性能。
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.
因此，即使通过JDBC将设置提取大小转换为SQL LIMIT子句，使用JDBC强制执行SQL查询的最大区别在于，您实际上仍然能够浏览所有结果。
#2
0
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.
当SQL server构建一个resultset作为查询的答案时，首先应用SQL LIMIT。它可能(通常也会)影响所使用的查询计划，因此也会影响SQL服务器响应时间。resultset只包含与限制相关的行——您不能获取更多。
Fetch size comes afterwards when content of the resultset is transfered to client. For details see Pascals answer.
当resultset的内容被转移到客户端时，获取大小随后会出现。详情请参阅帕斯卡回答。
#1
13
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.
SQL限制将把SQL查询结果限制在指定范围内。您可以使用它来显示第一个X个结果，或者显示X - Y结果的范围。
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.
fetch大小是JDBC驱动程序一次从数据库中检索到的行数，您可以在接下来的查询结果集中滚动。例如，您将查询的大小设置为100。当检索第一行时，JDBC驱动程序检索前100行(如果少于100行满足查询，则检索所有行)。当您检索第二行时，JDBC驱动程序仅仅从本地内存返回行—它不必从数据库中检索该行。这个特性通过减少对数据库的调用(通常是网络传输)来提高性能。
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.
因此，即使通过JDBC将设置提取大小转换为SQL LIMIT子句，使用JDBC强制执行SQL查询的最大区别在于，您实际上仍然能够浏览所有结果。
#2
0
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.
当SQL server构建一个resultset作为查询的答案时，首先应用SQL LIMIT。它可能(通常也会)影响所使用的查询计划，因此也会影响SQL服务器响应时间。resultset只包含与限制相关的行——您不能获取更多。
Fetch size comes afterwards when content of the resultset is transfered to client. For details see Pascals answer.
当resultset的内容被转移到客户端时，获取大小随后会出现。详情请参阅帕斯卡回答。