Saturday, January 7, 2012

Quest Performance for 1.7

Just a small teaser on the performance improvements that you can expect for the next release. We are so excited about them that we can't wait to tell.

Query containment: A critical part of query optimization in Quest relies on using query containment (CQC) to remove redundant queries from query rewriting. This is done by mutual checks, query1 vs query2 and so on. In complex cases, Quest might need to perform thousands or hundreds of thousands checks for a given query, hence the performance of the containment check algorithm is CRITICAL. We have been improving our algorithms and API to minimize the cost of this, so far we have a achieved a reduction of up to ... 95% of the cost! This means that if you got a complex query that was taking 12 s or 20 s to rewrite due to CQC, now it will just take 0.5 s or 1 s!

The best part is that what we have implemented until now is just a small fraction of all the optimization related to query containment we have in mind!

SQL Analysis: Up to version 1.6, we handled SQL as a black box. Any SQL query that was present in the mappings, was not really understood by Quest. The only thing Quest got from the query was the "signature", that is, the columns in the SELECT. Because of this, during the generation of the final SQL queries, Quest always had to rely on nesting of sub-queries. For example:

SELECT view1.x view2.y FROM 
 (SELECT x FROM employee) view1,
 (SELECT x,y FROM worksfor) view2
 view1.x = view2.y

This is, in general, not good for performance. In order to plan this kind of query, the DBMS like Postgres, DB2 and Oracle have to "flat" the query. If this is not done, chance are that the query plan for the query will be not good, index might not be used, join orders might be suboptimal. What is worse, if the DBMS doesn't implement query flattening, for example MySQL, then the nested views have to be materialized before usage, with no indexes or anything... VERY BAD PERFORMANCE.

The good news is, for version 1.7, Quest will include its brand new SQL analyzer, and updated code that makes use of the result of this analysis during query rewriting, and SQL query generation. The first version of the analyzer doesn't understand 100% of SQL, however, it is enough to cover around 80% of the SQL queries we have seen in most use cases. The result is that the SQL queries generated by quest will be much closer to what a human would write on its own, and the DBMS will be able to optimize much much better.

If you would like to give version 1.7 a test drive before it is released, please contact us.

No comments:

Post a Comment