I just benchmarked a query against an Oracle database using three
different access techniques (OLDDB, ADO.NET and ODBC) and I found that
ADO.NET fared worse than all of them - almost twice as long as OLEDB!
Surely something is wrong here. I was under the impression that ADO.NET
was supposed to improve retrieval times, not slow it down! The results
are as follows - it is an average of five executions of the same query.
Code is included in case someone wants to tell me what I'm doing wrong.
The query itself is a fairly long select query that I can't post here
due to its proprietary nature - the actual query shouldn't affect these
results, anyway.
Please, someone, what's going on?
-----------------------------------------------------------
OLEDB: 1.21 Seconds
-----------------------------------------------------------
public String execute_query (String query, DataGridView data_grid)
{
Int32 rowcount = 0;
OleDbCommand command = new OleDbCommand (query, connection);
DataTable data_table = new DataTable ();
OleDbDataAdapter data_reader = new OleDbDataAdapter ();
try {
data_reader.SelectCommand = command;
data_reader.Fill (data_table);
data_grid.DataSource = data_table;
rowcount = data_table.Rows.Count;
} catch (Exception e) {
MessageBox.Show ("Failed" + Environment.NewLine + e.Message);
} finally {
data_table.Dispose ();
data_reader.Dispose ();
command.Dispose ();
}// try..catch..finally
return "Rows returned: " + rowcount.ToString ();
}// execute_query;
-----------------------------------------------------------
ODBC: 1.274 Seconds
-----------------------------------------------------------
public String execute_query (String query) {
Int32 rowcount = 0;
OdbcCommand command = new OdbcCommand (query, connection);
DataTable data_table = new DataTable ();
OdbcDataAdapter data_reader = new OdbcDataAdapter ();
try {
data_reader.SelectCommand = command;
data_reader.Fill (data_table);
rowcount = data_table.Rows.Count;
} catch (Exception e) {
MessageBox.Show ("Failed" + Environment.NewLine + e.Message);
} finally {
data_table.Dispose ();
data_reader.Dispose ();
command.Dispose ();
}// try..catch..finally
return "Rows returned: " + rowcount.ToString ();
}// execute_query;
-----------------------------------------------------------
ADO.NET: 2.056 Seconds
-----------------------------------------------------------
public String execute_query (String query) {
Int32 rowcount = 0;
OracleCommand command = new OracleCommand (query, connection);
DataTable data_table = new DataTable ();
OracleDataAdapter data_reader = new OracleDataAdapter ();
try {
data_reader.SelectCommand = command;
data_reader.Fill (data_table);
rowcount = data_table.Rows.Count;
} catch (Exception e) {
MessageBox.Show ("Failed" + Environment.NewLine + e.Message);
} finally {
data_table.Dispose ();
data_reader.Dispose ();
command.Dispose ();
}// try..catch..finally
return "Rows returned: " + rowcount.ToString ();
}// execute_query;