Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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;

        }

    }

}

 

Print | posted on Thursday, May 12, 2005 9:37 AM |

Feedback

Gravatar

# .NET CrossTabs versus SQL Server CrossTabs

5/12/2005 10:02 AM | Welcome to Jeff's Blog
Gravatar

# .NET CrossTabs versus SQL Server CrossTabs

5/15/2005 10:14 PM | Welcome to Jeff's Blog
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET