Sign inTry Free

ESTIMATED_QUERY_RUNTIME

On this page

Returns an estimate of the elapsed time, in seconds, required to run a query.

The elapsed time includes the execution time for the query as well as the time required by any blocking operations that delay the query from executing.

This function is intended to be used inside a user-defined scalar function (UDF) that selects a resource pool.

Note

At this time, ESTIMATED_QUERY_RUNTIME is an experimental function. It is not formally supported. However, feedback and suggestions on this function are welcome.

Syntax

ESTIMATED_QUERY_RUNTIME()

Return Type

Double

Remarks

  • For ESTIMATED_QUERY_RUNTIME to provide an accurate estimate, statistics must first be collected on the query that this function operates on. After an initial run of the query, statistics are automatically collected at ten minute intervals. You can collect statistics immediately by running ANALYZE MEMORY.

  • Returns -1 if SingleStore cannot estimate the time required to run the query, because statistics on the query have not been collected.

Examples

Example 1: Using ESTIMATED_QUERY_RUNTIME() Inside a Resource Pool Selector Function

Setting Resource Limits provides an example of using ESTIMATED_QUERY_RUNTIME() inside of a user-defined scalar function (UDF). The UDF selects a resource pool dynamically.

Example 2: Using ESTIMATED_QUERY_RUNTIME() Outside a Resource Pool Selector Function

While ESTIMATED_QUERY_RUNTIME() is intended to be used inside a resource pool selector function, you can use it outside the function for testing. The following example estimates the elapsed time required to run three simple queries.

First, do an initial run of the three queries, followed by ANALYZE MEMORY to collect statistics on the queries. The initial run returns -1 because statistics have not yet been collected.

SELECT SLEEP(0.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(0.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
|          0 |                        -1 |
+------------+---------------------------+
SELECT SLEEP(1.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(1.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
|          0 |                        -1 |
+------------+---------------------------+
SELECT SLEEP(3.0), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(3.0) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
|          0 |                        -1 |
+------------+---------------------------+

Next, collect statistics on the queries:

ANALYZE MEMORY;

Re-run the previous query that sleeps for 0.5 seconds.

SELECT SLEEP(0.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(0.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
|          0 |                     0.507 |
+------------+---------------------------+

Re-run the previous query that takes 1.5 seconds.

SELECT SLEEP(1.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(1.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
|          0 |                     1.508 |
+------------+---------------------------+

Re-run the previous query that takes 3.0 seconds.

SELECT SLEEP(3.0), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(3.0) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
|          0 |                     3.009 |
+------------+---------------------------+

Last modified: August 21, 2024

Was this article helpful?

On this page

Was this article helpful?