示例#1
0
        public void TestDemoStatisticsTimeIO()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();
            GetSqlInfo     s = new GetSqlInfo();

            string constring_1 = u.GetConnectionString("AdventureWorks2014");

            string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.StatisticsTimeIO\\Demos\\DemoStatisticsTimeIO.sql");

            SqlConnection _conn = new SqlConnection(constring_1);

            _conn.InfoMessage += new SqlInfoMessageEventHandler(s.Message);


            for (int i = 0; i < batches.Length; i++)
            {
                _conn.Open();
                SqlCommand cmd_1 = new SqlCommand(batches[i], _conn);
                cmd_1.ExecuteNonQuery();
                _conn.Close();
            }

            MatchCollection q = Regex.Matches(s.SqlInfoMessages[1], @"\d+");

            Assert.IsTrue(Convert.ToInt32(q[1].ToString()) <= 1246);

            MatchCollection w = Regex.Matches(s.SqlInfoMessages[2], @"\d+");

            Assert.IsTrue(Convert.ToInt32(q[0].ToString()) <= 200);
        }
示例#2
0
        public void TestUnionUnionAllDemo2()
        {
            GetConnection      u  = new GetConnection();
            GetBatches         b  = new GetBatches();
            GetBatchResult     r  = new GetBatchResult();
            GetQueryPlan       qp = new GetQueryPlan();
            GetQueryPlanValues qv = new GetQueryPlanValues();

            string constring_1 = u.GetConnectionString("AdventureWorks2014");

            string[] batches      = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UnionUnionAll\\Demos\\UnionUnionAllDemo2.sql");
            string   SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            string   XPath        = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp[1]/@LogicalOp";

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 4)
                {
                    string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
                    string _operator = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath);
                    Assert.AreEqual("Distinct Sort", _operator);
                }
            }
        }
示例#3
0
        public void TestForcedParamDemo1()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();

            List <String> simpleExec = new List <String>();
            List <String> forcedExec = new List <String>();

            string constring_1 = u.GetConnectionString("SabinIO.ForcedParameterisation.Demo");

            string[] batches = b.GetFileContent(@"..\\..\\..\\QueryExecution\\demos\\ForcedParamDemo1.sql");

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 1)
                {
                    string t = ds.Tables["Table"].Rows[0]["is_parameterization_forced"].ToString();
                    Assert.AreEqual("False", t);
                }

                if (i == 7)
                {
                    for (int a = 0; a < ds.Tables["Table"].Rows.Count; a++)
                    {
                        simpleExec.Add(ds.Tables["Table"].Rows[a]["execution_count"].ToString());
                    }

                    Assert.AreEqual("10", simpleExec[0]);
                    Assert.AreEqual("10", simpleExec[1]);
                    Assert.AreEqual("10", simpleExec[2]);
                    Assert.AreEqual("1", simpleExec[3]);
                }

                if (i == 9)
                {
                    string t = ds.Tables["Table"].Rows[0]["is_parameterization_forced"].ToString();
                    Assert.AreEqual("True", t);
                }

                if (i == 15)
                {
                    for (int a = 0; a < ds.Tables["Table"].Rows.Count; a++)
                    {
                        forcedExec.Add(ds.Tables["Table"].Rows[a]["execution_count"].ToString());
                    }

                    Assert.AreEqual("20", forcedExec[0]);
                    Assert.AreEqual("10", forcedExec[1]);
                    Assert.AreEqual("1", forcedExec[2]);
                }
            }
        }
示例#4
0
        public void TestTruncateDeleteDemo1()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();
            GetSqlInfo     s = new GetSqlInfo();

            string constring_1 = u.GetConnectionString("SabinIO.Performance.TruncateDelete");

            string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.TruncateDelete\\Demos\\TruncateDeleteDemo1.sql");

            SqlConnection _conn = new SqlConnection(constring_1);

            _conn.InfoMessage += new SqlInfoMessageEventHandler(s.Message);

            for (int i = 0; i < batches.Length; i++)
            {
                _conn.Open();
                SqlCommand cmd_1 = new SqlCommand(batches[i], _conn);
                cmd_1.CommandTimeout = 60;
                cmd_1.ExecuteNonQuery();
                _conn.Close();
            }

            MatchCollection q = Regex.Matches(s.SqlInfoMessages[1], @"\d+");

            Assert.IsTrue(Convert.ToInt32(q[0].ToString()) <= 10);
            Assert.IsTrue(Convert.ToInt32(q[1].ToString()) <= 10);

            MatchCollection w = Regex.Matches(s.SqlInfoMessages[2], @"\d+");

            Assert.IsTrue(Convert.ToInt32(w[0].ToString()) <= 10);
            Assert.IsTrue(Convert.ToInt32(w[1].ToString()) <= 22);
            Assert.IsTrue(Convert.ToInt32(w[2].ToString()) <= 10);
            Assert.IsTrue(Convert.ToInt32(w[3].ToString()) <= 10);
            Assert.IsTrue(Convert.ToInt32(w[4].ToString()) <= 10);
            Assert.IsTrue(Convert.ToInt32(w[5].ToString()) <= 10);
            Assert.IsTrue(Convert.ToInt32(w[6].ToString()) <= 10);
        }
示例#5
0
        public void TestArithAbortDemo1()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();

            string constring_1 = u.GetConnectionString("SabinIO.ArithAbort.PlanCache");

            string[]      batches = b.GetFileContent(@"..\\..\\..\\SabinIO.ArithAbort.PlanCache\\demos\\ArithAbortDemo1.sql");
            List <String> value   = new List <String>();
            List <String> exec    = new List <String>();

            for (int i = 0; i < batches.Length; i++)
            {
                string batch = batches[i];
                if (i == 0)
                {
                    batch = batch.Replace("100", "5");
                }
                if (i == 1)
                {
                    batch = batch.Replace("50", "2");
                }
                DataSet ds = r.GetDataSet(batch, constring_1);

                if (@i == 1)
                {
                    for (int a = 0; a < ds.Tables["Table"].Rows.Count; a++)
                    {
                        value.Add(ds.Tables["Table"].Rows[a]["value"].ToString());
                        exec.Add(ds.Tables["Table"].Rows[a]["execution_count"].ToString());
                    }
                    Assert.AreEqual("251", value[0]);
                    Assert.AreEqual("4347", value[1]);
                    Assert.AreEqual("10", exec[0]);
                    Assert.AreEqual("2", exec[1]);
                }
            }
        }
示例#6
0
        public void TestOutOfDateStatistics_Demo2()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();

            string constring_1 = u.GetConnectionString("SabinIO.Statistics.OutOfDate");

            string[] batches   = b.GetFileContent(@"..\\..\\..\\SabinIO.Statistics.OutOfDate\\demos\\OutOfDateStatistics_Demo2.sql");
            int      Rows      = 0;
            string   StatsDate = string.Empty;


            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 1)
                {
                    Rows      = Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]);
                    StatsDate = Convert.ToString(ds.Tables["table"].Rows[0]["column1"]);
                }

                if (i == 4 || i == 7 || i == 10)
                {
                    Assert.IsTrue(Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]) > Rows);
                    Assert.AreEqual(Convert.ToString(ds.Tables["table"].Rows[0]["column1"]), StatsDate);
                    //increase row count for next test in this loop
                    Rows = Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]);
                }

                if (i == 13)
                {
                    Assert.IsTrue(Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]) > Rows);
                    Assert.AreNotEqual(Convert.ToString(ds.Tables["table"].Rows[0]["column1"]), StatsDate);
                }
            }
        }
示例#7
0
        public void TestForcedParamDemo2()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();

            string constring_1 = u.GetConnectionString("AdventureWorks2014");

            string[] batches = b.GetFileContent(@"..\\..\\..\\QueryExecution\\demos\\ForcedParamDemo2.sql");

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 5)
                {
                    DataRow[] adhoc = ds.Tables["Table"].Select(@"objtype = 'Adhoc'");
                    Assert.AreEqual(2, adhoc.Length);
                    Assert.AreEqual(1, Convert.ToInt32(adhoc[0]["usecounts"]));
                    Assert.AreEqual(1, Convert.ToInt32(adhoc[1]["usecounts"]));

                    DataRow[] Prepared = ds.Tables["Table"].Select(@"objtype = 'Prepared'");
                    Assert.AreEqual(1, Prepared.Length);
                    Assert.AreEqual(2, Convert.ToInt32(Prepared[0]["usecounts"]));
                    Assert.AreNotEqual(Convert.ToString(adhoc[0]["query_plan"]), Convert.ToString(Prepared[0]["query_plan"]));
                    Assert.AreNotEqual(Convert.ToString(adhoc[1]["query_plan"]), Convert.ToString(Prepared[0]["query_plan"]));
                }

                if (i == 10)
                {
                    DataRow[] adhoc = ds.Tables["Table"].Select(@"objtype = 'Adhoc'");
                    Assert.AreEqual(2, adhoc.Length);
                    Assert.AreEqual(1, Convert.ToInt32(adhoc[0]["usecounts"]));
                    Assert.AreEqual(1, Convert.ToInt32(adhoc[1]["usecounts"]));
                }
            }
        }
示例#8
0
        public void TestUDFDemo3()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();

            string constring_1 = u.GetConnectionString("AdventureWorks2014");

            string[] batches            = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UDF\\Demos\\Udf_Demo3.sql");
            long     batchThreeDuration = 0;
            long     batchSixDuration   = 0;

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 3)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    DataSet ds_5 = r.GetDataSet(batch, constring_1);
                    sw.Stop();
                    batchThreeDuration = sw.ElapsedMilliseconds;
                }

                if (i == 6)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    DataSet ds_5 = r.GetDataSet(batch, constring_1);
                    sw.Stop();
                    batchSixDuration = sw.ElapsedMilliseconds;

                    Assert.IsTrue(batchThreeDuration > batchSixDuration);
                }
            }
        }
示例#9
0
        public void TestUDFDemo1()
        {
            GetConnection  u = new GetConnection();
            GetBatches     b = new GetBatches();
            GetBatchResult r = new GetBatchResult();

            string constring_1 = u.GetConnectionString("SabinIO.Performance.UDF");

            string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UDF\\Demos\\Udf_Demo1.sql");

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 3)
                {
                    DataView  v = new DataView(ds.Tables["table"]);
                    DataTable distinctValues = v.ToTable(true, "CurDate");
                    //Ive seen cases of values looking identical but not being handled as identical, so the count just confirms that everything is identical
                    int count = Convert.ToInt32(distinctValues.Rows.Count);
                    if (count == 1)
                    {
                        Assert.AreEqual(1, Convert.ToInt32(distinctValues.Rows.Count));
                    }

                    if (count == 2)
                    {
                        Assert.AreEqual(ds.Tables["Table"].Rows[0]["CurDate"], ds.Tables["Table"].Rows[1]["CurDate"]);
                    }
                }
                if (i == 5)
                {
                    Assert.IsTrue(ds.Tables["Table"].Rows.Count >= 2);
                }
            }
        }
示例#10
0
        public void TestParameterisationDemo()
        {
            GetConnection      u = new GetConnection();
            GetBatches         b = new GetBatches();
            GetBatchResult     r = new GetBatchResult();
            GetQueryPlanValues q = new GetQueryPlanValues();

            string constring_1 = u.GetConnectionString("AdventureWorks2014");

            string[] batches      = b.GetFileContent(@"..\\..\\..\\SabinIO.Parameterisation\\demos\\Parameterisation.Demo1.sql");
            string   XPath        = "//sql:Batch/sql:Statements/sql:StmtSimple/" + "sql:QueryPlan[1]/sql:RelOp[1]/@EstimateRows";
            string   SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            string   ConvertQueryPlan;
            string   Batch6EstimatedRows  = String.Empty;
            string   Batch9EstimatedRows  = String.Empty;
            string   Batch12EstimatedRows = String.Empty;
            string   Batch15EstimatedRows = String.Empty;

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 4)
                {
                    Assert.AreEqual(2, ds.Tables["Table"].Rows.Count);
                    DataRow[] adhoc = ds.Tables["Table"].Select(@"objtype = 'Adhoc'");
                    Assert.AreEqual(2, adhoc.Length);
                }
                if (i == 6)
                {
                    Assert.AreEqual(3, ds.Tables["Table"].Rows.Count);
                    DataRow[] Prepared = ds.Tables["Table"].Select(@"objtype = 'Prepared'");
                    Assert.AreEqual(1, Prepared.Length);
                    ConvertQueryPlan = Convert.ToString(Prepared[0]["query_plan"]);

                    Batch6EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
                }
                if (i == 9 || i == 12 || i == 15)
                {
                    Assert.AreEqual(1, ds.Tables["Table"].Rows.Count);
                    DataRow[] Prepared = ds.Tables["Table"].Select(@"objtype = 'Prepared'");
                    ConvertQueryPlan = Convert.ToString(Prepared[0]["query_plan"]);
                    if (i == 9)
                    {
                        Batch9EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
                        Assert.AreNotEqual(Batch6EstimatedRows, Batch9EstimatedRows);
                    }
                    if (i == 12)
                    {
                        Batch12EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
                        Assert.AreEqual(Batch6EstimatedRows, Batch12EstimatedRows);
                    }
                    if (i == 15)
                    {
                        Batch15EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
                        Assert.AreNotEqual(Batch12EstimatedRows, Batch15EstimatedRows);
                        Assert.AreNotEqual(Batch6EstimatedRows, Batch15EstimatedRows);
                    }
                }
            }
        }
示例#11
0
        public void TestUDFDemo2()
        {
            GetConnection      u  = new GetConnection();
            GetBatches         b  = new GetBatches();
            GetBatchResult     r  = new GetBatchResult();
            GetQueryPlan       qp = new GetQueryPlan();
            GetQueryPlanValues qv = new GetQueryPlanValues();

            string constring_1 = u.GetConnectionString("AdventureWorks2014");

            string[] batches               = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UDF\\Demos\\Udf_Demo2.sql");
            string   SqlNamespace          = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            string   XPath                 = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp/sql:StreamAggregate/sql:RelOp/sql:Top/sql:RelOp/@LogicalOp";
            long     batchFiveDuration     = 0;
            long     batchSixDuration      = 0;
            long     batchTwelveDuration   = 0;
            long     batchThirteenDuration = 0;

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 5)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    DataSet ds_5 = r.GetDataSet(batch, constring_1);
                    sw.Stop();
                    batchFiveDuration = sw.ElapsedMilliseconds;

                    string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
                    string _operator = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath);
                    Assert.AreEqual("Compute Scalar", _operator);
                }

                if (i == 6)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    DataSet ds_6 = r.GetDataSet(batch, constring_1);
                    sw.Stop();
                    batchSixDuration = sw.ElapsedMilliseconds;
                    Assert.IsTrue(batchFiveDuration < batchSixDuration);
                }

                if (i == 12)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    DataSet ds_5 = r.GetDataSet(batch, constring_1);
                    sw.Stop();
                    batchTwelveDuration = sw.ElapsedMilliseconds;
                    Assert.IsTrue(batchTwelveDuration < batchSixDuration);
                }

                if (i == 13)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    DataSet ds_6 = r.GetDataSet(batch, constring_1);
                    sw.Stop();
                    batchThirteenDuration = sw.ElapsedMilliseconds;
                    Assert.IsTrue(batchTwelveDuration <= batchThirteenDuration);
                }
            }
        }
示例#12
0
        public void TestOutOfDateStatistics_Demo1()
        {
            GetConnection      u  = new GetConnection();
            GetBatches         b  = new GetBatches();
            GetBatchResult     r  = new GetBatchResult();
            GetQueryPlan       qp = new GetQueryPlan();
            GetQueryPlanValues qv = new GetQueryPlanValues();

            string constring_1 = u.GetConnectionString("SabinIO.Statistics.OutOfDate");

            string[] batches       = b.GetFileContent(@"..\\..\\..\\SabinIO.Statistics.OutOfDate\\demos\\OutOfDateStatistics_Demo1.sql");
            string   SqlNamespace  = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            string   XPath         = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp[1]/@EstimateRows";
            string   XPathForSproc = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:StoredProc/sql:Statements/sql:StmtSimple[2]/sql:QueryPlan[1]/sql:RelOp[1]/@EstimateRows";

            for (int i = 0; i < batches.Length; i++)
            {
                string  batch = batches[i];
                DataSet ds    = r.GetDataSet(batch, constring_1);

                if (i == 6)
                {
                    Assert.AreEqual(0, Convert.ToInt32(ds.Tables["table"].Rows[0]["Column1"]));
                    string QueryPlan   = qp.GetQueryPlanForQuery(constring_1, batch);
                    string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPath);
                    Assert.AreEqual("1", RowEstimate);
                }

                if (i == 7)
                {
                    Assert.AreEqual(0, Convert.ToInt32(ds.Tables["table"].Rows[0]["RowsAffected"]));
                    string QueryPlan   = qp.GetQueryPlanForQuery(constring_1, batch);
                    string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPathForSproc);
                    Assert.AreEqual("1", RowEstimate);
                }
                if (i == 9 || i == 11)
                {
                    Assert.AreEqual(100, Convert.ToInt32(ds.Tables["table"].Rows[0]["RowsAffected"]));
                    string QueryPlan   = qp.GetQueryPlanForQuery(constring_1, batch);
                    string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPathForSproc);
                    Assert.AreEqual("1", RowEstimate);
                }

                if (i == 4 || i == 12)
                {
                    DateTime d = new DateTime(2016, 01, 10, 23, 45, 36);
                    DateTime t = Convert.ToDateTime(ds.Tables["table"].Rows[199]["RANGE_HI_KEY"]);
                    Assert.AreEqual(200, ds.Tables["table"].Rows.Count);
                    Assert.AreEqual(d, t);
                }

                if (i == 14)
                {
                    DateTime d = new DateTime(2016, 01, 11, 23, 45, 36);
                    DateTime t = Convert.ToDateTime(ds.Tables["table"].Rows[199]["RANGE_HI_KEY"]);
                    Assert.AreEqual(200, ds.Tables["table"].Rows.Count);
                    Assert.AreEqual(d, t);
                }

                if (i == 15)
                {
                    Assert.AreEqual(100, Convert.ToInt32(ds.Tables["table"].Rows[0]["RowsAffected"]));
                    string QueryPlan   = qp.GetQueryPlanForQuery(constring_1, batch);
                    string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPathForSproc);
                    Assert.AreEqual("99.125", RowEstimate);
                }
            }
        }