Saved Queries

The save_query command serves to both generate and store an execution plan, offering time savings for the execution of frequently used complex queries. It’s important to note that the saved execution plan is closely tied to the structure of its underlying tables. Consequently, if any of the objects mentioned in the query undergo modification, the saved query must be recreated.

Saved queries undergo compilation during their creation. When executed, these queries utilize the precompiled query plan instead of compiling a new plan at query runtime.

Syntax

Saved queries related syntax:

-- Saving a query
SELECT SAVE_QUERY(saved_query_name, parameterized_query_string)

-- Showing a saved query
SELECT SHOW_SAVED_QUERY(saved_query_name)

-- Listing saved queries
SELECT LIST_SAVED_QUERIES()

-- Executing a saved query
SELECT EXECUTE_SAVED_QUERY(saved_query_name, [ , argument [ , ... ] ] )

-- Dropping a saved query
SELECT DROP_SAVED_QUERY(saved_query_name)

saved_query_name ::= string_literal
parameterized_query_string ::= string_literal
argument ::= string_literal | number_literal

Parameter Support

Query parameters can be used as substitutes for constants expressions in queries.

Parameters cannot be used to substitute identifiers like column names and table names.
Query parameters of a string datatype must be of a fixed length and may be used in equality checks but not with patterns such as LIKE and RLIKE.

Permissions

Statement / Function	Permission
SAVE QUERY	Saving queries requires no special permissions per se, however, it does require from the user to have permissions to access the tables referenced in the query and other query element permissions. The user who saved the query is granted all permissions on the saved query.
SHOW SAVED QUERY	Showing a saved query requires `SELECT` permissions on the saved query.
LIST SAVED QUERIES	Listing saved queries requires no special permissions.
EXECUTE SAVED QUERY	Executing a saved query requires `USAGE` permissions on the saved query and `SELECT` permissions to access the tables referenced in the query.
DROP SAVED QUERY	Dropping a saved query requires `DDL` permissions on the saved query and `SELECT` permissions to access the tables referenced in the query.

Parameterized Query

Parameterized queries, also known as prepared statements, enable the usage of parameters which may be replaced by actual values when executing the query. They are created and managed in application code, primarily to optimize query execution, enhance security, and allow for the reuse of query templates with different parameter values.

SELECT SAVE_QUERY('select_by_weight_and_team','SELECT * FROM nba WHERE Weight > ? AND Team = ?');