skip to content

A JMeter JDBC Request runs a reporting query returning 400,000 rows. What does the sampler build?

level: seniorimportance: should knowfreq 44%

answer

  1. The response body is not optional
  2. Named variables are extra, not instead
  3. One field bounds the rows read
  4. Its default is no limit at all

basics

~20 s

It walks the whole result set and concatenates every row into one tab-separated string as the sample's response data, whether or not Variable Names is filled. Limit ResultSet is the field that caps how many rows it reads.

solid answer

~50 s

A `Select Statement` or `Prepared Select Statement` JDBC Request always builds the sample's response data from the result set: a header line of column labels, then every row, values tab-separated. That happens regardless of `Variable Names` and regardless of which listeners are attached, so a 400,000-row report becomes one very large string in the injector's heap for the duration of the sample. On top of that, `Variable Names` writes one thread variable per row per named column, and `Result Variable Name` builds a list holding one map per row keyed by column label. **Limit ResultSet** is the brake: it defaults to empty, which means `-1` and no limit, and a positive value both calls `setMaxRows` on the statement and stops the row loop early. The manual is explicit that the limit affects every downstream option — you get an incomplete result set and a row count at or below the limit — which is exactly the trade you are making.

code

text · 5 lines
text
region	orders	revenue
EMEA	10422	488210.55
APAC	 9317	331980.00
AMER	11045	502117.90
... one line per row, for every row in the result set ...

go deeper

for a junior

Recall that a select's rows all end up in the sample's response data, and that Limit ResultSet is the field that caps how many rows the sampler reads.

for a middle

Explain that the response string is built whether or not Variable Names is set, and that Variable Names and Result Variable Name add per-row storage on top of it rather than replacing it.

for a senior

Diagnose an injector whose heap tracks concurrent JDBC threads, and choose between shrinking the query, bounding it with Limit ResultSet and Query timeout, and saying so in the results.

for a principal

Own what a direct-to-database plan is allowed to claim in your team's reporting, and set the rule for which queries a plan may hammer without a caller-shaped row bound.

Driving a reporting query straight at a database with no application in front of it is the classic way to make a JMeter injector fall over, and the reason is in the sampler, not in the database. ## What a select builds For `Select Statement` and `Prepared Select Statement`, the JDBC Request sampler iterates the entire result set and assembles a single string: first the column labels tab-separated, then one line per row with the values tab-separated. That string becomes the sample's response data. Three things follow. - It is built **unconditionally**. Leaving `Variable Names` and `Result Variable Name` blank does not stop it. - It is built **in full before the sample ends**, so the peak lives in the injector's heap while the sampler is still running. - Byte-array columns are decoded to text on the way in, so a binary column does not stay compact. ## What the optional fields add on top | Field | What it costs when the result set is large | |---|---| | `Variable Names` | one thread variable per row for every named column, plus one `_#` count variable per name | | `Result Variable Name` | a list with one map per row, each map holding every column keyed by its label | | `Handle ResultSet` | only applies to `ResultSet`-typed values from callable statements; `Store as String` is the default | | `Limit ResultSet` | the only field that reduces how many rows are read | Both `Variable Names` and `Result Variable Name` are additive: they hold their data for the rest of the thread's iteration, on top of the response string. ## Limit ResultSet is the brake `Limit ResultSet` is empty by default, which the sampler reads as `-1`, meaning no limit. Give it a positive number and two things happen: `setMaxRows` is called on the statement, so the driver is asked to stop fetching, and the sampler's own row loop stops once it has processed that many rows. The manual states the consequence bluntly — the limit affects all the options that depend on the result set, so you get an incomplete result set and a record count at or below the limit. That is the honest trade: you are choosing to stop measuring the full transfer. The neighbouring fields are worth reading in the same breath. `Query timeout (s)` is empty by default, which the sampler turns into `0` — `setQueryTimeout(0)`, meaning no limit — while `-1` means the sampler does not call `setQueryTimeout` at all, which some drivers need. So a runaway report has no time bound out of the box either. ## Reading the run Symptoms that point at this rather than at the database: 1. Injector heap climbing with the number of concurrent JDBC threads rather than with the run's duration. 2. Sample elapsed times that scale with the row count of the query rather than with its complexity. 3. Very large response data in any result file that was configured to save bodies. 4. `OutOfMemoryError` in `jmeter.log` while the database itself is comfortable. And the shape of the fix, in order of preference: - Make the query return what a real caller would ask for — the sampler measures whatever you asked the database for, and a report that no caller would request in one page is not the thing to hammer. - Set `Limit ResultSet` when you deliberately want to bound the transfer, and record that you did, because the sample no longer covers the full result. - Set `Query timeout (s)` so a pathological plan does not hold a connection indefinitely. - Leave `Variable Names` and `Result Variable Name` empty unless something downstream actually reads them. ## The caveat this leaf exists for A JDBC Request plan drives the database through the vendor driver and the DBCP pool that live **inside the JMeter JVM**. Everything the sample's elapsed time covers — acquiring a connection, executing the statement, and streaming and stringifying every row — happens in the injector and at the database. There is no application server in the path, so the numbers characterise the database and its driver as JMeter exercises them, and nothing else. That is a useful measurement, and it is a different measurement from one taken through a service.

  • What are the defaults of Limit ResultSet and Query timeout (s) on a JDBC Request?
    Both are empty. An empty `Limit ResultSet` is read as `-1`, meaning no row limit; an empty `Query timeout (s)` is read as `0`, which is passed to `setQueryTimeout` and means no timeout. A value of `-1` on the timeout field is special: it tells the sampler not to call `setQueryTimeout` at all, for drivers that do not support it.
  • Does leaving Variable Names blank stop the sampler from reading the whole result set?
    No. The sampler walks every row to build the sample's response data regardless of that field, so a blank Variable Names saves you thread variables but not the fetch, the transfer or the string. Only `Limit ResultSet` reduces the number of rows actually read.
  • Where does Handle ResultSet fit in?
    It governs values of `ResultSet` type returned by callable statements, choosing between `Store as String` (the default), `Store as Object` and `Count Records`. It does not change how an ordinary select builds its response data, and CLOB and BLOB values it stores are truncated at the `jdbcsampler.max_retain_result_size` limit, 65536 bytes by default.

saying these in an interview costs you the question

  • Thinks blank Variable Names stops the rows being read
  • Assumes Limit ResultSet defaults to some safe number
  • Believes only attached listeners hold the rows
  • Expects an untimed query to be cut off automatically
  • Treats a database-only plan as a measurement of the service