SQLClient vs OleDb
{
// 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.
