Reduced execution time for driver-limited result sets
Details
| Detail name | Value |
|---|---|
| Changelog Number | 6348 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | Exasol 7.0.0, Exasol 7.0.rc2 |
| Resolution Date | 2020-08-26 |
Background
The Exasol ODBC and JDBC drivers provide interfaces to limit the number of rows in result sets returned to the client. But the implementation did not reduce the execution time of queries returning bigger result sets. This can cause slow responses for big result sets in clients like DbVisualizer.
Improvement
We reduced the execution time for SELECT queries returning big result sets which are limited by the following settings:
- ODBC driver: SQLSetStmtAttr() for attribute SQL_ATTR_MAX_ROWS
- JDBC driver: setMaxRows() and setLargeMaxRows()
- ADO.NET driver: EXACommand.MaxRows (added in this improvement)
- WebSockets API v2: Attribute resultSetMaxRows (added in this improvement, requires additional implementation in the driver)
Each of these settings can be used to configure the maximum number of rows in any result set returned by the corresponding object. Any additional rows are discarded. A value of zero means no limit (this is the default).
Restrictions
There is no performance gain in the following cases:
- SELECT queries which would not benefit from adding a LIMIT clause.
- If the outermost SELECT query is combined with other queries using table operators like UNION.
- If the outermost SELECT query contains a UDF call in the SELECT list and has no ORDER BY.
The column ROW_COUNT in the system table EXA_SQL_LAST_DAY may be used to check whether a query was optimized by the database.
