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