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