ESTIMATED_QUERY_RUNTIME

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.

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 MemSQL 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

The topic 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 |
+------------+---------------------------+

Was this Article Helpful?

Thanks For Your Feedback!