How can I modify or tune a Hyper-Q query?

Datometry Hyper-Q's query override feature enables DBAs to register a custom query that will be executed on the downstream database when a client submits a specific input query. Query override can be used when the required performance cannot be achieved on the target database through the standard optimization techniques or by restructuring the original client query.

The example below shows how to register an override query with Datometry Hyper-Q.

Step 1: Run the original query:

SELECT 'This is the original input query';

  *** Query completed. One row found. One column returned.
  *** Total elapsed time was 1 second.


  'This is the original input query'
  ----------------------------------
  This is the original input query

Step 2: Find the query in the Hyper-Q query tracer log and locate the query hash under the tag name (column 6) "DLE0033--Client Query Hash" that shares the same client request ID (column 3) as the client query record. In this example, the query hash is e8cb3b802caa15e19f1a18a54315d53b:

"2020-10-01 06:36:34.693","31","41230236-3983-4f45-a4f3-cf2c4322e4d1","","DLC002--Preprocessing","DLE0033--Client Query Hash","e8cb3b802caa15e19f1a18a54315d53b"
"2020-10-01 06:36:34.693","31","41230236-3983-4f45-a4f3-cf2c4322e4d1","","DLC002--Preprocessing","DLE0003--Start Client Query","SELECT 'This is the original input query';"

Step 3: Insert the override query into the Hyper-Q metadata store, providing the query hash identified above, the override query, and an optional comment:

INSERT INTO "__DTM_MDSTORE"."MDSTORE_QUERY_MAP_TABLE" VALUES ('e8cb3b802caa15e19f1a18a54315d53b', 'SELECT ''This is the override query'';', 'For demonstration only.');

Step 4: Reload the override table in Hyper-Q. Currently, a restart of the Hyper-Q service is required to reload the override table.

From now on, every time a query identical to the original query is submitted to Hyper-Q, the override query is executed on the downstream database and the results returned:

SELECT 'This is the original input query';

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

This is the override query

Important Notes:

• The query text must be identical for the override to be applied. This includes but is not limited to white spaces (e.g., Windows-style newlines do not match Unix-style newlines), keywords (e.g., SEL does not match SELECT), and optional quoting of identifiers (e.g., "mytable" does not match mytable).
• When an override query is found, all processing performed by Hyper-Q is bypassed and the override query is submitted as is. This includes, but is not limited to, supporting enhanced schema properties such as non-constant default expressions. It is the responsibility of the DBA who maintains the override queries to keep them in sync with potential future changes that could affect the format or correctness of query results. For example, when the non-constant default expression for an underlying column changes, the respective change must be added to all overridden DML statements that insert or update that column.
• Override queries run in the same session that the client submitting the original query has connected as. Override queries are therefore subject to permissions of the user of this session.
• The comment column of the query map table in the Hyper-Q metadata store can be used to record arbitrary information. Datometry recommends adding information that facilitates maintaining the override queries such as a reference to the application or script that contains the original query and the reason for the override.
• Datometry recommends starting with the original translation generated by Hyper-Q and modifying it as required by, e.g., adding a query hint.
• Due to the nature of the query override feature, Hyper-Q cannot guarantee that the override query returns the correct results for the original query.
• The feature is mainly designed for single SELECT statements. Other statements may not behave as expected.
• SELECT statements trigger the return of a result set, which is converted and returned to the client. All other statements, including DML statements like INSERT, UPDATE, and DELETE, are reported to the client like an INSERT statement and do not return a result set.