CrossTab Performance Testing
using System;
using System.Data.SqlClient;
using System.Data;
namespace CSharpConsole
{
class CrossTabTesting
{
[STAThread]
static void Main(string[] args)
{
SqlConnection conn;
string s;
int i;
conn = new SqlConnection( "Server=(local);Database=Northwind;uid=xx;pwd=xx");
conn.Open();
Console.WriteLine("Beginning performance test for crosstab techniques.\n");
Console.WriteLine("Please enter the # of iterations to perform:");
s = Console.ReadLine();
try
{
i = Convert.ToInt32(s);
Console.WriteLine("\nTesting dynamic SQL crosstab procedure....");
Console.WriteLine("-- Result: {0} seconds.\n", TestCrossTabDynamic(conn, i));
Console.WriteLine("Testing static SQL crosstab procedure....");
Console.WriteLine("-- Result: {0} seconds.\n", TestCrossTabStatic(conn, i));
Console.WriteLine("Testing C# crosstab transformation....");
Console.WriteLine("-- Result: {0} seconds.\n", TestPivotFunction(conn, i));
Console.WriteLine("\nTesting complete.");
}
catch (Exception ex)
{
Console.WriteLine("\nError: " + ex.Message);
Console.WriteLine("Program aborted...");
}
finally
{
conn.Close();
Console.WriteLine("\nPress ENTER to quit.");
Console.ReadLine();
}
}
private static int TestPivotFunction(SqlConnection conn, int iterations)
{
DataTable dt;
String SQL = "select * from PivotTest order by CustomerID";
SqlCommand com = new SqlCommand(SQL, conn);
DateTime t = DateTime.Now;
while (iterations-- > 0)
dt = Pivot(com.ExecuteReader(),"CustomerID","ProductName","Qty");
return DateTime.Now.Subtract(t).Seconds;
}
private static int TestCrossTabDynamic(SqlConnection conn, int iterations)
{
SqlDataAdapter da;
DataTable dt;
string SQL = "exec CrossTab2 'select * from PivotTest','ProductName','SUM(Qty)[]','CustomerID, CompanyName'";
SqlCommand com = new SqlCommand(SQL, conn);
DateTime t = DateTime.Now;
while (iterations-- > 0)
{
dt = new DataTable();
da = new SqlDataAdapter(com);
da.Fill(dt);
}
return DateTime.Now.Subtract(t).Seconds;
}
private static int TestCrossTabStatic(SqlConnection conn, int iterations)
{
SqlDataAdapter da;
DataTable dt;
// set the parameter so we get a SQL string only back that we can execute directly:
string SQL = "exec CrossTab2 'select * from PivotTest','ProductName','SUM(Qty)[]','CustomerID, CompanyName',Null,1";
SqlCommand com = new SqlCommand(SQL, conn);
SQL = com.ExecuteScalar().ToString();
// and set up the new SQL command, already written out and optimized and ready to go:
com = new SqlCommand(SQL,conn);
DateTime t = DateTime.Now;
while (iterations-- > 0)
{
dt = new DataTable();
da = new SqlDataAdapter(com);
da.Fill(dt);
}
return DateTime.Now.Subtract(t).Seconds;
}
public static DataTable Pivot(IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)
{
DataTable tmp = new DataTable();
DataRow r;
string LastKey = "//dummy//";
int i, pValIndex, pNameIndex;
string s;
bool FirstRow = true;
// Add non-pivot columns to the data table:
pValIndex = dataValues.GetOrdinal(pivotValueColumn);
pNameIndex = dataValues.GetOrdinal(pivotNameColumn);
for (i=0; i <= dataValues.FieldCount -1; i++)
if (i != pValIndex && i != pNameIndex )
tmp.Columns.Add(dataValues.GetName(i),dataValues.GetFieldType(i));
r = tmp.NewRow();
// now, fill up the table with the data:
while (dataValues.Read())
{
// see if we need to start a new row
if (dataValues[keyColumn].ToString() != LastKey)
{
// if this isn't the very first row, we need to add the last one to the table
if (!FirstRow)
tmp.Rows.Add(r);
r = tmp.NewRow();
FirstRow = false;
// Add all non-pivot column values to the new row:
for (i=0; i<= dataValues.FieldCount-3;i++)
r[i] = dataValues[tmp.Columns[i].ColumnName];
LastKey = dataValues[keyColumn].ToString();
}
// assign the pivot values to the proper column; add new columns if needed:
s = dataValues[pNameIndex].ToString();
if (!tmp.Columns.Contains(s))
tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex));
r[s] = dataValues[pValIndex];
}
// add that final row to the datatable:
tmp.Rows.Add(r);
// Close the DataReader
dataValues.Close();
// and that's it!
return tmp;
}
}
}