0

When query using JDBC createStatement and getting ResultSet of millions of rows (from SQL Server), what SQL queries are really executed, where do the rows are stored and how many rows? Does it use SQL Server cursor? I know that only a limited number of rows are kept in the client-side memory (where the Java code), so where are the rest of the rows kept, or maybe they are not yet queried from the SQL Server)? When I looked at SQL Server profiler, it just shows the normal query (it didn't add cursor or TOP X of something else)

Tal Glik
  • 480
  • 2
  • 11
  • Could you please elaborate your problem in detail by using any example? – Noman ali abbasi Apr 11 '20 at 06:34
  • In java using JDBC, I'm sending a query to the SQL Server that return ResultSet. In my java code, I'm using a loop. something like: while (resultSet.nexy()). Suppose my query return millions of rows, where do those rows are kept? how the SQL execute this query? does it use SQL cursor? – Tal Glik Apr 11 '20 at 08:46
  • Please [edit] your question, don't provide additional information in the comments. – Mark Rotteveel Apr 11 '20 at 16:19
  • It is not additional info, it is just to try to clarify the same question – Tal Glik Apr 11 '20 at 17:08

1 Answers1

1

Taken from the SQL Server documentation on Adaptive Buffering:

Normally, when the Microsoft JDBC Driver for SQL Server executes a query, the driver retrieves all of the results from the server into application memory. Although this approach minimizes resource consumption on the SQL Server, it can throw an OutOfMemoryError in the JDBC application for the queries that produce very large results.

There's a way around that, what SQL Server calls Adaptive Buffering and which seems to be enabled by default for JDBC driver version 2+ and you might want to read up on it in the documentation. Here's the gist:

In order to allow applications to handle very large results, the Microsoft JDBC Driver for SQL Server provides adaptive buffering. With adaptive buffering, the driver retrieves statement execution results from the SQL Server as the application needs them, rather than all at once.

Marco Behler
  • 3,627
  • 2
  • 17
  • 19
  • I still do not understand what does the SQL does in adaptive buffering mode? how it queries the database and when, does it keep the full result in temp table? – Tal Glik Apr 11 '20 at 09:02
  • From the client-side, there is no difference. You execute the SQL, that's it. How adaptive buffering works internally in SQL server I do not know, however. – Marco Behler Apr 11 '20 at 09:04
  • The reason I ask is to verify that the SQL Server will not go into out of memory by itself if I make several big queries simultaneously with ResultSet – Tal Glik Apr 11 '20 at 09:14