fskorgen opened a new issue, #8229:
URL: https://github.com/apache/hop/issues/8229
### Apache Hop version?
2.19
### Java version?
21
### Operating system
Windows
### What happened?
SqlEditor` caps a SELECT at 1000 rows:
```java
List<Object[]> rows = db.getRows(sql.getStatement(), 1000);
```
That limit only ever reaches the **read loop**. `Database.getRows(String,
int)` delegates to
`getRows(sql, limit, null)`, which runs `openQuery(...)` and then stops
reading after `limit` rows in
`getRows(ResultSet, limit, IProgressMonitor)`. Nothing tells the driver.
The driver is told by `setMaxRows`, and `openQuery` only calls it when the
connection carries a row
limit:
```java
if (rowlimit > 0 && databaseMeta.supportsSetMaxRows()) {
pstmt.setMaxRows(rowlimit);
}
```
`rowlimit` comes from `Database.setQueryLimit(int)`, which `SqlEditor` never
calls. So the statement
goes to the server unbounded, the driver materialises the full result, and
only then are the first
1000 rows handed back and the rest discarded.
### Why it is worse than it sounds
The default `SELECT * FROM <table>` is exactly the shape a user types in a
SQL editor, and the
JDBC drivers that buffer a result set client-side by default — SQL Server's
among them — read the
entire table before returning row one. `SqlEditor` runs behind a modal
`ProgressMonitorDialog`, so
HopGui is unusable for the duration, and on a large fact table the JVM can
run out of heap for rows
nobody asked for.
Measured on a 60-column MSSQL table through this code path: **~1 minute to
return 100 rows**
without `setQueryLimit`, immediate with it.
The query timeout does not save it either. `setQueryTimeout` bounds
statement *execution*, not the
row-fetch loop, so a statement that returns quickly and then streams for a
minute is never
interrupted.
### Why this is an oversight rather than a choice
The class next door already does it correctly.
`GetPreviewTableProgressDialog`, added in 2.18:
```java
database.setStatementQueryTimeoutSeconds(queryTimeoutSeconds);
...
database.setQueryLimit(limit);
```
Preview therefore bounds the fetch server-side; the SQL editor does not.
Both are user-facing
row-limited reads in the same package, and only one tells the driver.
### Steps to reproduce
1. Point a connection at SQL Server (or any driver that buffers by default)
holding a table with
millions of rows and a wide row.
2. Open the SQL editor on that connection — e.g. from the database explorer
dialog.
3. Run `SELECT * FROM <that table>`.
**Expected:** roughly the cost of 1000 rows; the editor's own cap is what
makes this safe to type.
**Actual:** the full table is transferred before anything appears, HopGui is
blocked behind the modal
progress dialog, and heap use tracks the whole result rather than 1000 rows.
Comparing `SHOW PROFILE`/server-side row counts, or simply watching the
fetch time against
`SELECT TOP 1000 *`, shows the limit is not reaching the server.
### Suggested fix
Set the limit on the connection before the script runs, in
`runSqlScriptWithMonitor`, next to the
timeout it already sets:
```java
if (timeoutSeconds > 0) {
db.setStatementQueryTimeoutSeconds(timeoutSeconds);
}
db.setQueryLimit(1000);
db.connect();
```
`setMaxRows` is guarded by `supportsSetMaxRows()`, so dialects that do not
support it (MonetDB
overrides it to false) are unaffected and keep today's client-side
behaviour. The 1000 would be
better as the row limit the user chose rather than a constant, but that is a
separate improvement —
the defect is that the existing constant never leaves the JVM.
### Issue Priority
Priority: 2
### Issue Component
Component: Hop Gui
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
