Matching Queries

The CData Virtuality Server includes a matching component that evaluates incoming user queries, estimates their structure, and checks for identity with previously issued queries. It recognizes all structural components of an issued query and stores an appropriate row in the RecommendedOptimizations table for each of them.

For example, let us consider the following query:

SELECT year(h.ORDERDATE),sum(d.linetotal) AS s
FROM oracle_DB.SALESORDERDETAIL d JOIN adventureworks_part_three.salesorderheader h ON d.SalesOrderID=h.SalesOrderID 
GROUP BY year(h.ORDERDATE)
ORDER BY year(h.ORDERDATE);

Its execution will result in two recommended optimizations:

Both of the created recommended optimizations are of type MAT_JOIN, meaning that an appropriate (sub-)structure of the query represents a JOIN of two elements. Another type of a recommended optimization is TABLE, which is created when a request for a single table like SELECT * FROM datasourceA.tableX is issued.

The two created recommended optimizations have a frequency of 1. This means that the appropriate JOINs have been issued only once before. When you repeat the original issue, both counters in the Freq column are increased by one. Another request with a similar structure will also increase the appropriate counter.

Let us consider the same query as above, but with different aliases and/or changed sides of the JOIN:

SELECT year(hh.ORDERDATE),sum(dd.linetotal) AS s 
FROM oracle_DB.SALESORDERDETAIL dd JOIN adventureworks_part_three.salesorderheader hh ON dd.SalesOrderID=hh.SalesOrderID 
GROUP BY year(hh.ORDERDATE) 
ORDER BY year(hh.ORDERDATE);

SELECT year(h.ORDERDATE),sum(d.linetotal) AS s
FROM adventureworks_part_three.salesorderheader h JOIN oracle_DB.SALESORDERDETAIL d ON d.SalesOrderID=h.SalesOrderID
GROUP BY year(h.ORDERDATE)
ORDER BY year(h.ORDERDATE);

The structure of the particular query will still be the same, the appropriate counters will be increased, and no additional recommended optimization will be created.