際際滷

際際滷Share a Scribd company logo

EP to SQL Compiler

?
?
R
Ryan Marcus

The document describes a tool called EPtoSQL that can compile physical execution plans built by optimizers like DevelOPs into valid SQL queries that can be tested on Microsoft SQL Server. It works by translating logical operators in a physical plan like merge joins and loop joins into their SQL Server equivalents like MERGE JOIN and INNER LOOP JOIN, and by addressing complications that can arise during translation like handling selections over joins. The overall procedure involves subexpression elimination and ensuring semantics are preserved at each step.

1 of 19
Download to read offline
Execution Plan Compiler Brionne Godby Rachel Leeman-Munk Ryan Marcus
Motivation ¢ DevelOPs lets optimizers build execution plans
Motivation ¢ DevelOPs lets optimizers build physical plans ¢ ´ but there¨s no way of testing those plans!
Motivation ¢ DevelOPs lets optimizers build physical plans ¢ ´ but there¨s no way of testing those plans! ¢ Enter: EPtoSQL!
SQL Server ¢ Microsoft SQL Server lets you force join order ＄ OPTION(FORCE ORDER) ¢ And join types´ ＄ SELECT * FROM t1 MERGE JOIN t2 ON ...
DevelOPs Physical Plans Merge Join Loop Join table1 table2 table3
DevelOPs Physical Plans Merge Join Loop Join table1 table2 table3 PMJOIN(attr1 = attr2, PNLJOIN(a3 = a4, PTABLE(table1), PTABLE(table2) ), PTABLE(table3) )
Translating Physical Plans PMJOIN(attr1 = attr2, PNLJOIN(a3 = a4, PTABLE(table1), PTABLE(table2) ), PTABLE(table3) ) SELECT * FROM table2 INNER LOOP JOIN table1 ON a3 = a4 INNER MERGE JOIN table3 ON attr1 = attr2 OPTION(FORCE ORDER);
Translating Physical Plans PMJOIN(attr1 = attr2, PNLJOIN(a3 = a4, PTABLE(table1), PTABLE(table2) ), PTABLE(table3) ) SELECT * FROM table2 INNER LOOP JOIN table1 ON a3 = a4 INNER MERGE JOIN table3 ON attr1 = attr2 OPTION(FORCE ORDER);
Translating Physical Plans PMJOIN(attr1 = attr2, PNLJOIN(a3 = a4, PTABLE(table1), PTABLE(table2) ), PTABLE(table3) ) SELECT * FROM table2 INNER LOOP JOIN table1 ON a3 = a4 INNER MERGE JOIN table3 ON attr1 = attr2 OPTION(FORCE ORDER);
Translating Physical Plans ¢ Some complications ＄ Selection over join with same attributes ＄ Join over selection with same attributes
Translating Physical Plans ¢ Some complications ＄ Selection over join with same attributes ＄ Join over selection with same attributes ¢ Subexpression elimination
Subexpression Elimination Merge Join Loop Join table1 table2 table3
Subexpression Elimination Merge Join Loop Join table1 table2 table3 Merge Join table3 #T1
General Procedure
General Procedure Semantics after IR?
General Procedure Subexpression elimination Semantics after IR?
General Procedure Subexpression elimination Everything valid if each subexpression valid
GPL¨d! ¢ Code on GitHub ＄ https://github.com/RyanMarcus/DevelOPSToSQLServerCompiler

More Related Content

EP to SQL Compiler