Home > Uncategorized > SQLClient vs OleDb

SQLClient vs OleDb

I’ve read that SQLClient is more efficient than OleDb, but I was curious to know how much actual performance increase it would give in a real world environment.
 
I plucked 40 SQL statements from a trace at random, and ran them both through code for OLEDB and SQLClient respectively,

private void button1_Click(object sender, System.EventArgs e)

{

// Native

string strDSN = "Data Source=10.0.10.3;Initial Catalog=CFSpain;User ID=;Password=;Connection Reset=FALSE;";

SqlConnection conDb = new SqlConnection(strDSN);

DateTime dtStart = DateTime.Now;

conDb.Open();

string[] strSqlBenchmarks = Regex.Split(this.tbSQL.Text,"gorn");

foreach (string strSqlBenchmark in strSqlBenchmarks)

{

try

{

SqlDataAdapter daDb =

new SqlDataAdapter(strSqlBenchmark,conDb);

DataSet dsDb = new DataSet();

daDb.Fill(dsDb);

}

catch(Exception ex)

{

System.Diagnostics.Debug.WriteLine(ex);

}

}

conDb.Close();

TimeSpan tsLength = dtStart – DateTime.Now;

MessageBox.Show("Time elapsed:" + tsLength.Seconds);

}

private void btnGeneric_Click(object sender, System.EventArgs e)

{

// Generic

string strDSN = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Initial Catalog=CFSpain;Data Source=10.0.10.3;Connection Reset=FALSE;";

OleDbConnection conDb =

new OleDbConnection(strDSN);

DateTime dtStart = DateTime.Now;

conDb.Open();

string[] strSqlBenchmarks = Regex.Split(this.tbSQL.Text,"gorn");

foreach (string strSqlBenchmark in strSqlBenchmarks)

{

try

{

OleDbDataAdapter daDb = new OleDbDataAdapter(strSqlBenchmark,conDb);

DataSet dsDb =

new DataSet();

daDb.Fill(dsDb);

}

catch(Exception ex)

{

System.Diagnostics.Debug.WriteLine(ex);

}

}

conDb.Close();

TimeSpan tsLength = dtStart – DateTime.Now;

MessageBox.Show("Time elapsed:" + tsLength.Seconds);

}

 

The results were quite dissapointing, with either technique taking somewhere between 7 to 11 seconds over the same set of SQL statements. Adding these results up over 8 tests each, I came up with a 5% improvement with SqlClient over OleDb adapter.

 

In the particular project I was working on, this performance gain was too low to justify an overhaul of the system.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: