Link to home
Start Free TrialLog in
Avatar of purplesoup
purplesoupFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to run performance tests for .NET Db Libraries

I want to write some tests to compare performance of accessing a SQL Server database using different .NET Db libraries - in particular OleDb and SQL Server.

Can anyone point me to a list of dos and don'ts for running performance tests? I'm thinking that perhaps there is some way to turn off caching for example to ensure it is the actual Db access that is being measured.

Thanks for any help you can give.
SOLUTION
Avatar of richard_hughes
richard_hughes
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of purplesoup

ASKER

I've run two groups of tests, comparing the SQL Client library with the OleDb library.

The first group of tests uses a Command object which has an INSERT statement assigned, parameters set, and 500 rows inserted.

First I used a table with one ID UNIQUEIDENTIFIER PRIMARY KEY and 989 DATETIME NULL columns.

The average SQL performance to insert 500 rows was 13.62 seconds, the average OLEDB time was 2.86.

Next I used a table with one ID UNIQUEIDENTIFIER PRIMARY KEY and 350 NVARCHAR(10 NOT NULL columns.

The average SQL performance to insert 500 rows was 5.11 seconds, the average OLEDB time was 2.19.

I've included the StringInsert code to give an example of what was run.

Clearly, OleDb is actually faster than the native SQL library. Can anyone explain why this might be??

The second group of tests use a DataAdapter which is populated with data from a SQL table containing around 10,000 rows.

At first just the 10,000 rows were returned, with results as follows:

OLE        SQL
4.39              2.53
4.21              2.71
4.26              2.55
4.40              2.69
4.32              2.62

This makes the SQL 1.65 times faster than the OLEDB.

I then modified the test so that as well as returning 10,000 rows, 5000 rows are updated, 500 rows inserted and the data adapter Update command is then run.

Test results are as follows (in seconds):

OLE      SQL
90      84
87      82
96      95
83      89
-------------
89      87.5

As you can see, the SQL is marginally faster, but not much.

I had thought things would be much faster with SQL - for example this test

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/fadb2742-d75a-49fb-a839-b2d4a9183998

claims SQL to be 2.5 times faster than OLEDB.

Can anyone give some comments on my tests to see whether I am missing something?

private void StringInsert(ClientClassesEnum clientType, int recs)
        {
            const int C_COLUMNS = 350;

            if (recs <= 0) recs = 500;

            IDbConnection c;
            if (clientType == ClientClassesEnum.SqlClient)
            {
                c = new SqlConnection();
            }
            else
            {
                c = new OleDbConnection();
            }

            string conn = Helper.GetConnectionString("DataSource.iMIS.Connection");

            if (clientType == ClientClassesEnum.OleDbClient)
            {
                c.ConnectionString = "Provider=SQLOLEDB;" + conn;    
            }
            else
            {
                c.ConnectionString = conn;    
            }
            
            c.Open();

            IDbCommand cmd;
            if (clientType == ClientClassesEnum.SqlClient)
            {
                cmd = new SqlCommand();
            }
            else
            {
                cmd = new OleDbCommand();    
            }
            
            cmd.Connection = c;

            StringBuilder cmdTxt = new StringBuilder();
            cmdTxt.Append("INSERT INTO PerformanceTable2 (");
            cmdTxt.Append("ID,");

            // Generate the SQL INSERT string columns
            for (int i = 1; i <= C_COLUMNS; i++)
            {
                string s1 = "String%1".Replace("%1", i.ToString());
                cmdTxt.Append(s1);
                if (i < C_COLUMNS) cmdTxt.Append(",");
            }
            cmdTxt.Append(") VALUES (");

            if (clientType == ClientClassesEnum.SqlClient)
            {
                cmdTxt.Append("@ID,");    
            }
            else
            {
                cmdTxt.Append("?,");    
            }

            // Generate the SQL INSERT string values
            for (int i = 1; i <= C_COLUMNS; i++)
            {
                string s1;
                if (clientType == ClientClassesEnum.SqlClient)
                {
                    s1 = "@P%1".Replace("%1", i.ToString());    
                }
                else
                {
                    s1 = "?";    
                }
                cmdTxt.Append(s1);
                if (i < C_COLUMNS) cmdTxt.Append(",");
            }
            cmdTxt.Append(")");

            cmd.CommandText = cmdTxt.ToString();

            // pass in the ID value
            IDataParameter p;
            if (clientType == ClientClassesEnum.SqlClient)
            {
                p = new SqlParameter("ID", SqlDbType.UniqueIdentifier);               
            }
            else            
            {
                p = new OleDbParameter("ID", OleDbType.Guid);
            }
            p.Value = Guid.NewGuid();
            cmd.Parameters.Add(p);

            for (int i = 1; i <= C_COLUMNS; i++)
            {
                if (clientType == ClientClassesEnum.SqlClient)
                {
                    string s1 = "@P%1".Replace("%1", i.ToString());
                    // p = new SqlParameter(s1, SqlDbType.DateTime);
                    p = new SqlParameter(s1, SqlDbType.NVarChar);
                }
                else
                {
                    string s1 = "P%1".Replace("%1", i.ToString());
                    // p = new OleDbParameter(s1, OleDbType.Date);
                    p = new OleDbParameter(s1, OleDbType.VarWChar);
                }
                p.Value = DateTime.Now.ToShortDateString();
                cmd.Parameters.Add(p);                
            }

            Stopwatch w = new Stopwatch();
            w.Start();

            int r = cmd.ExecuteNonQuery();

            for (int j = 1; j < 500; j++)
            {
                if (clientType == ClientClassesEnum.SqlClient)
                {
                    (cmd.Parameters[0] as SqlParameter).Value = Guid.NewGuid();
                }
                else
                {
                    (cmd.Parameters[0] as OleDbParameter).Value = Guid.NewGuid();    
                }
                
                for (int i = 1; i <= C_COLUMNS; i++)
                {
                    if (clientType == ClientClassesEnum.SqlClient)
                    {
                        (cmd.Parameters[i] as SqlParameter).Value = DateTime.Now.AddMinutes(i).ToShortDateString();
                    }
                    else
                    {
                        (cmd.Parameters[i] as OleDbParameter).Value = DateTime.Now.AddMinutes(i).ToShortDateString();
                    }
                }

                int r2 = cmd.ExecuteNonQuery();
            }

            w.Stop();

            TimeSpan ts = w.Elapsed;
            txtResults.Text += String.Format("{0:00}:{1:00}:{2:00}.{3:00}\n",
                                ts.Hours, ts.Minutes, ts.Seconds,
                                ts.Milliseconds / 10);

        }

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial