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); } } }
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); } } } }
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); } } }
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); } } }