示例#1
0
        static void Main(string[] args)
        {
            Catalog.Init();

            string sql = "";

            if (args.Length != 0)
            {
                sql = args[0];
            }

#pragma warning disable CS0162 // Unreachable code detected
            // The warnings are annoying, so using a pragma to suppress them.
            if (false)
            {
                JOBench.CreateTables();
                var stats_fn = "../../../../jobench/statistics/jobench_stats";
                Catalog.sysstat_.read_serialized_stats(stats_fn);
                sql = File.ReadAllText("../../../../jobench/10a.sql");
                goto doit;
            }

            if (false)
            {
                Tpch.CreateTables();
                Tpch.LoadTables("0001");
                //Tpch.CreateIndexes();
                Tpch.AnalyzeTables();
                sql = File.ReadAllText("../../../../tpch/q20.sql");
                goto doit;
            }

            if (false)
            {
                //84
                sql = File.ReadAllText("../../../../tpcds/q33.sql");
                Tpcds.CreateTables();
                Tpcds.LoadTables("tiny");
                Tpcds.AnalyzeTables();
                // long time: 4 bad plan
                // 6: distinct not supported, causing wrong result
                // q23, q33, q56, q60: in-subquery plan bug
                // 10,11,13, 31, 38, 41, 48, 54, 66, 72, 74: too slow
                goto doit;
            }
#pragma warning restore CS0162 // Unreachable code detected

doit:
            bool convMode = false;

            // Application Arguments in Project properties seem to be
            // effective only after rebuilding.
            // This is a last ditch effort to be able to debug arbitrary
            // statements without rebuilding the solution.
            string inputFile = "";
            if (sql.Length == 2 && sql == "-i")
            {
                convMode = true;
            }
            else if (sql.Length == 2 && sql.StartsWith("-f"))
            {
                inputFile = args[1];
            }
            else if (sql.Length == 0)
            {
                sql = "select * from a tablesample row (2);";
            }

            do
            {
                if (convMode == true || (sql.Length == 1 && sql.Equals("-")))
                {
                    System.Console.Write("QSQL> ");
                    sql = System.Console.ReadLine();
                    System.Console.WriteLine(sql);
                }

                var datetime = new DateTime();
                datetime = DateTime.Now;

                var stopWatch = new Stopwatch();
                stopWatch.Start();

                if (inputFile.Length != 0)
                {
                    // read the file and execute all statements in it.
                    RunSQLFromFile(inputFile);
                    goto done;
                }

                // query options might be conflicting or incomplete
                Console.WriteLine(sql);
                var a = RawParser.ParseSingleSqlStatement(sql);
                ExplainOption.show_tablename_ = true;
                a.queryOpt_.profile_.enabled_ = true;
                a.queryOpt_.optimize_.enable_subquery_unnest_ = true;
                a.queryOpt_.optimize_.remove_from_            = true;
                a.queryOpt_.optimize_.use_memo_                  = true;
                a.queryOpt_.optimize_.enable_cte_plan_           = true;
                a.queryOpt_.optimize_.use_codegen_               = false;
                a.queryOpt_.optimize_.memo_disable_crossjoin_    = false;
                a.queryOpt_.optimize_.memo_use_joinorder_solver_ = false;
                a.queryOpt_.explain_.show_output_                = true;
                a.queryOpt_.explain_.show_id_      = true;
                a.queryOpt_.explain_.show_estCost_ = a.queryOpt_.optimize_.use_memo_;
                a.queryOpt_.explain_.mode_         = ExplainMode.full;

                // -- Semantic analysis:
                //  - bind the query
                a.queryOpt_.optimize_.ValidateOptions();

                if (!(a is SelectStmt))
                {
                    SQLStatement.ExecSQLList(sql);
                    goto done;
                }

                a.Bind(null);

                // -- generate an initial plan
                var rawplan = a.CreatePlan();
                Console.WriteLine("***************** raw plan *************");
                Console.WriteLine(rawplan.Explain());

                // -- optimize the plan
                PhysicNode phyplan = null;
                if (a.queryOpt_.optimize_.use_memo_)
                {
                    Console.WriteLine("***************** optimized plan *************");
                    var optplan = a.SubstitutionOptimize();
                    Console.WriteLine(optplan.Explain(a.queryOpt_.explain_));
                    a.optimizer_ = new Optimizer(a);
                    a.optimizer_.ExploreRootPlan(a);
                    phyplan = a.optimizer_.CopyOutOptimalPlan();
                    Console.WriteLine(a.optimizer_.PrintMemo());
                    Console.WriteLine("***************** Memo plan *************");
                    Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
                }
                else
                {
                    // -- optimize the plan
                    Console.WriteLine("-- optimized plan --");
                    var optplan = a.SubstitutionOptimize();
                    Console.WriteLine(optplan.Explain(a.queryOpt_.explain_));

                    // -- physical plan
                    Console.WriteLine("-- physical plan --");
                    phyplan = a.physicPlan_;
                    Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
                }

                // -- output profile and query result
                Console.WriteLine("-- profiling plan --");
                var final = new PhysicCollect(phyplan);
                a.physicPlan_ = final;
                ExecContext context = a.CreateExecContext();

                final.ValidateThis();
                if (a is SelectStmt select)
                {
                    select.OpenSubQueries(context);
                }

                final.Open(context);
                final.Exec(null);
                final.Close();

                if (a.queryOpt_.optimize_.use_codegen_)
                {
                    CodeWriter.WriteLine(context.code_);
                    Compiler.Run(Compiler.Compile(), a, context);
                }
                Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
done:
                stopWatch.Stop();
                Console.WriteLine("RunTime: " + stopWatch.Elapsed);
            } while (convMode == true);

            Console.ReadKey();
        }
示例#2
0
        static void Main(string[] args)
        {
            Catalog.Init();

            string sql = "";

            if (false)
            {
                JOBench.CreateTables();
                sql = File.ReadAllText("../../../jobench/10a.sql");
                goto doit;
            }

            if (false)
            {
                Tpch.CreateTables();
                Tpch.LoadTables("0001");
                //Tpch.CreateIndexes();
                Tpch.AnalyzeTables();
                sql = File.ReadAllText("../../../tpch/q20.sql");
                goto doit;
            }

            if (false)
            {
                Tpcds.CreateTables();
                Tpcds.LoadTables("tiny");
                Tpcds.AnalyzeTables();
                // 1, 2,3,7,10,
                // long time: 4 bad plan
                // 6: distinct not supported, causing wrong result
                sql = File.ReadAllText("../../../tpcds/q7.sql");
                goto doit;
            }

doit:
            sql = "select a2,b2,c2,d2 from ad, bd, cd, dd where a2=b2 and c2 = b2 and c2=d2 order by a2";
            sql = "select count(*) from ast group by tumble(a0, interval '10' second)";
            sql = "select round(a1, 10), count(*) from a group by round(a1, 10)";
            sql = "select count(*) from a group by round(a1, 10)";
            sql = "select count(*) from ast group by hop(a0, interval '5' second, interval '10' second)";
            sql = "select round(a1, 10) from a group by a1;";
            sql = "select abs(-a1*2), count(*) from a group by round(a1, 10);";
            sql = "select abs(-a1*2), count(*) from a group by a1;";
            sql = "select tumble_start(a0, interval '10' second), tumble_end(a0, interval '10' second), count(*) from ast group by tumble(a0, interval '10' second)";

            var datetime = new DateTime();

            datetime = DateTime.Now;

            var stopWatch = new Stopwatch();

            stopWatch.Start();

            // query options might be conflicting or incomplete
            Console.WriteLine(sql);
            var a = RawParser.ParseSingleSqlStatement(sql);

            ExplainOption.show_tablename_ = false;
            a.queryOpt_.profile_.enabled_ = true;
            a.queryOpt_.optimize_.enable_subquery_unnest_ = false;
            a.queryOpt_.optimize_.remove_from_            = false;
            a.queryOpt_.optimize_.use_memo_                  = true;
            a.queryOpt_.optimize_.enable_cte_plan_           = false;
            a.queryOpt_.optimize_.use_codegen_               = false;
            a.queryOpt_.optimize_.memo_disable_crossjoin_    = false;
            a.queryOpt_.optimize_.memo_use_joinorder_solver_ = false;
            a.queryOpt_.explain_.show_output_                = true;
            a.queryOpt_.explain_.show_id_   = false;
            a.queryOpt_.explain_.show_cost_ = a.queryOpt_.optimize_.use_memo_;

            // -- Semantic analysis:
            //  - bind the query
            a.queryOpt_.optimize_.ValidateOptions();
            a.Bind(null);

            // -- generate an initial plan
            var rawplan = a.CreatePlan();

            Console.WriteLine("***************** raw plan *************");
            Console.WriteLine(rawplan.Explain());

            // -- optimize the plan
            PhysicNode phyplan = null;

            if (a.queryOpt_.optimize_.use_memo_)
            {
                Console.WriteLine("***************** optimized plan *************");
                var optplan = a.SubstitutionOptimize();
                Console.WriteLine(optplan.Explain(a.queryOpt_.explain_));
                a.optimizer_.InitRootPlan(a);
                a.optimizer_.OptimizeRootPlan(a, null);
                Console.WriteLine(a.optimizer_.PrintMemo());
                phyplan = a.optimizer_.CopyOutOptimalPlan();
                Console.WriteLine(a.optimizer_.PrintMemo());
                Console.WriteLine("***************** Memo plan *************");
                Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
            }
            else
            {
                // -- optimize the plan
                Console.WriteLine("-- optimized plan --");
                var optplan = a.SubstitutionOptimize();
                Console.WriteLine(optplan.Explain(a.queryOpt_.explain_));

                // -- physical plan
                Console.WriteLine("-- physical plan --");
                phyplan = a.physicPlan_;
                Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
            }

            // -- output profile and query result
            Console.WriteLine("-- profiling plan --");
            var final = new PhysicCollect(phyplan);

            a.physicPlan_ = final;
            ExecContext context = a.CreateExecContext();

            final.ValidateThis();
            if (a is SelectStmt select)
            {
                select.OpenSubQueries(context);
            }
            var code = final.Open(context);

            code += final.Exec(null);
            code += final.Close();

            if (a.queryOpt_.optimize_.use_codegen_)
            {
                CodeWriter.WriteLine(code);
                Compiler.Run(Compiler.Compile(), a, context);
            }
            Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));

            stopWatch.Stop();
            Console.WriteLine("RunTime: " + stopWatch.Elapsed);
            Console.ReadKey();
        }
示例#3
0
        static void Main(string[] args)
        {
            Catalog.Init();

            string sql = "";

            //TestTpcds_LoadData();

            if (false)
            {
                JOBench.CreateTables();
                sql = File.ReadAllText("../../../jobench/10a.sql");
                goto doit;
            }

            if (false)
            {
                Tpch.CreateTables();
                Tpch.LoadTables("0001");
                //Tpch.CreateIndexes();
                Tpch.AnalyzeTables();
                sql = File.ReadAllText("../../../tpch/q20.sql");
                goto doit;
            }

            if (true)
            {
                Tpcds.CreateTables();
                //Tpcds.LoadTables("tiny");
                //Tpcds.AnalyzeTables();
                // 1, 2,3,7,10,
                // long time: 4 bad plan
                // 6: distinct not supported, causing wrong result
                sql = File.ReadAllText("../../../tpcds/q7.sql");
                goto doit;
            }

doit:
            sql = "with cte as (select * from d) select * from cte where d1=1;";
            sql = "with cte as (select * from a) select cte1.a1, cte2.a2 from cte cte1, cte cte2 where cte2.a3<3";  // ok
            sql = "with cte as (select * from a) select * from cte cte1, cte cte2 where cte1.a2=cte2.a3 and cte1.a1> 0 order by 1;";
            sql = "select ab.a1, cd.c1 from (select * from a join b on a1=b1) ab , (select * from c join d on c1=d1) cd where ab.a1=cd.c1";
            sql = "select * from (select avg(a2) from a join b on a1=b1) a (a1) join b on a1=b1;";
            sql = "with cte as (select * from a join b on a1=b1 join c on a2=c2) select * from cte cte1, cte cte2;"; // ok
            sql = "with cte as (select count(*) from a join b on a1=b1) select * from cte cte1;";                    // ok
            sql = "with cte as (select count(*) from a join b on a1=b1) select * from cte cte1, cte cte2;";
            sql = "with cte as (select * from d where d1=1) select * from cte cte1, cte cte2;";
            sql = "with cte as (select * from a where a1=1) select * from cte cte1, cte cte2;";

            var stopWatch = new Stopwatch();

            stopWatch.Start();

            Console.WriteLine(sql);
            var a = RawParser.ParseSingleSqlStatement(sql);

            a.queryOpt_.profile_.enabled_ = true;
            a.queryOpt_.optimize_.enable_subquery_unnest_ = true;
            a.queryOpt_.optimize_.remove_from_            = false;
            a.queryOpt_.optimize_.use_memo_        = true;
            a.queryOpt_.optimize_.enable_cte_plan_ = false;
            a.queryOpt_.optimize_.use_codegen_     = false;

            //a.queryOpt_.optimize_.memo_disable_crossjoin = false;
            //a.queryOpt_.optimize_.use_joinorder_solver = true;

            // -- Semantic analysis:
            //  - bind the query
            a.queryOpt_.optimize_.ValidateOptions();
            a.Bind(null);

            // -- generate an initial plan
            ExplainOption.show_tablename_     = false;
            a.queryOpt_.explain_.show_output_ = false;
            a.queryOpt_.explain_.show_cost_   = a.queryOpt_.optimize_.use_memo_;
            var rawplan = a.CreatePlan();

            Console.WriteLine("***************** raw plan *************");
            Console.WriteLine(rawplan.Explain(0));

            physic.PhysicNode phyplan = null;
            if (a.queryOpt_.optimize_.use_memo_)
            {
                Console.WriteLine("***************** optimized plan *************");
                var optplan = a.SubstitutionOptimize();
                Console.WriteLine(optplan.Explain(0, a.queryOpt_.explain_));
                a.optimizer_.InitRootPlan(a);
                a.optimizer_.OptimizeRootPlan(a, null);
                Console.WriteLine(a.optimizer_.PrintMemo());
                phyplan = a.optimizer_.CopyOutOptimalPlan();
                Console.WriteLine(a.optimizer_.PrintMemo());
                Console.WriteLine("***************** Memo plan *************");
                Console.WriteLine(phyplan.Explain(0, a.queryOpt_.explain_));
            }
            else
            {
                // -- optimize the plan
                Console.WriteLine("-- optimized plan --");
                var optplan = a.SubstitutionOptimize();
                Console.WriteLine(optplan.Explain(0, a.queryOpt_.explain_));

                // -- physical plan
                Console.WriteLine("-- physical plan --");
                phyplan = a.physicPlan_;
                Console.WriteLine(phyplan.Explain(0, a.queryOpt_.explain_));
            }

            Console.WriteLine("-- profiling plan --");
            var final = new PhysicCollect(phyplan);

            a.physicPlan_ = final;
            var context = new ExecContext(a.queryOpt_);

            final.ValidateThis();
            if (a is SelectStmt select)
            {
                select.OpenSubQueries(context);
            }
            var code = final.Open(context);

            code += final.Exec(null);
            code += final.Close();

            if (a.queryOpt_.optimize_.use_codegen_)
            {
                CodeWriter.WriteLine(code);
                Compiler.Run(Compiler.Compile(), a, context);
            }
            Console.WriteLine(phyplan.Explain(0, a.queryOpt_.explain_));

            stopWatch.Stop();
            Console.WriteLine("RunTime: " + stopWatch.Elapsed);
            Console.ReadKey();
        }