Saved queries

Saved queries are a way to store a query’s execution plan for later re-use.

Tip

  • Use saved queries to share queries between different users, or to eliminate the need to rewrite a query that is run frequently.
  • Use saved queries to reduce the risk of SQL injection attacks

How saved queries work

Saved queries are compiled when they are created. The query plan is saved in SQream DB’s metadata for later re-use.

Because the query plan is saved, they can be used to reduce compilation overhead, especially with very complex queries.

When executed, the saved query plan is recalled and executed. on the up-to-date data stored on disk.

Parameters support

To enhance their capability, saved queries can contain query parmeters that are replaced upon execution. Query parameters can be used as substitutes for literal expressions.

There are a few limitations however:

  • Parameters cannot be used to substitute identifiers, column names, table names, or other parts of the query.
  • Query parameters of a string datatype (like VARCHAR) must be of a fixed length, and can be used in equality checks, but not patterns (e.g. LIKE, RLIKE, etc)

Saving a query

Saving a query is performed by using the SAVE_QUERY statement.

Saving a simple query

t=> SELECT SAVE_QUERY('select_all','SELECT * FROM nba');
executed

Saving a parametrized query

Use parameters to replace them later at execution time.

Tip

Use dollar quoting ($$) to avoid escaping strings.

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

Listing and executing saved queries

Saved queries are saved as a database objects. They can be listed in one of two ways:

Using the catalog:

t=> SELECT * FROM sqream_catalog.savedqueries;
name                      | num_parameters
--------------------------+---------------
select_all                |              0
select_by_weight          |              1
select_by_weight_and_team |              2

Using the LIST_SAVED_QUERIES utility function:

t=> SELECT LIST_SAVED_QUERIES();
saved_query
-------------------------
select_all
select_by_weight
select_by_weight_and_team

Executing a saved query requires calling it by it’s name in a EXECUTE_SAVED_QUERY statement. A saved query with no parameter is called without parameters.

t=> SELECT EXECUTE_SAVED_QUERY('select_all');
Name                     | Team                   | Number | Position | Age | Height | Weight | College               | Salary
-------------------------+------------------------+--------+----------+-----+--------+--------+-----------------------+---------
Avery Bradley            | Boston Celtics         |      0 | PG       |  25 | 6-2    |    180 | Texas                 |  7730337
Jae Crowder              | Boston Celtics         |     99 | SF       |  25 | 6-6    |    235 | Marquette             |  6796117
John Holland             | Boston Celtics         |     30 | SG       |  27 | 6-5    |    205 | Boston University     |
R.J. Hunter              | Boston Celtics         |     28 | SG       |  22 | 6-5    |    185 | Georgia State         |  1148640
[...]

Executing a saved query with parameters requires specifying the parameters in the order they appear in the query:

t=> SELECT EXECUTE_SAVED_QUERY('select_by_weight_and_team', 240, 'Toronto Raptors');
Name              | Team            | Number | Position | Age | Height | Weight | College     | Salary
------------------+-----------------+--------+----------+-----+--------+--------+-------------+--------
Bismack Biyombo   | Toronto Raptors |      8 | C        |  23 | 6-9    |    245 |             | 2814000
James Johnson     | Toronto Raptors |      3 | PF       |  29 | 6-9    |    250 | Wake Forest | 2500000
Jason Thompson    | Toronto Raptors |      1 | PF       |  29 | 6-11   |    250 | Rider       |  245177
Jonas Valanciunas | Toronto Raptors |     17 | C        |  24 | 7-0    |    255 |             | 4660482