Long Query from OracleDB

Question

I have created a query to oracle db

Dictionary<decimal, decimal> Dict = new Dictionary<decimal, decimal>();
string strSelectIdWork = "SELECT COLUMN FROM my_tb WHERE ROW='" + Row + "'";
dataAdapter.Fill(ds, "my_tb");
foreach (DataRow row in ds.Tables["my_tb"].Rows)
{
  foreach (DataColumn column in ds.Tables["my_tb"].Columns)
  {
    Dict.Add(Dict.Count + 1, Convert.ToDecimal(row[column]));
  }
}
foreach (decimal someVar in Dict.Values)
{
  OleDbCommand command = myAccessConn.CreateCommand();
  OleDbTransaction trans = myAccessConn.BeginTransaction();
  command.Transaction = trans;
  command.CommandText = "SELECT COLUMN FROM my_tb2 WHERE ROW='" + someVar + "'";
  command.ExecuteNonQuery();
  nb = Convert.ToString(command.ExecuteScalar());
  comboBox2.Items.Add(nb;
  trans.Commit();
}

It's working, but it takes a long time to execute and I have many queries in my function. How can I change the code to reduce the time of the request?

What you are trying to do, can you please describe in words? — Mohammad Ashfaq, Feb 24 '14 at 06:54
That looks like something that you could probably do in a single query but, as suggested, actually describing what you're trying to achieve rather than expecting us to work it out from convoluted code with no comments would be a good idea. — jmcilhinney, Feb 24 '14 at 06:59
User select word = Row; programm get request to table where ROW = user word, then programm get request to another table with data from the first query. and add second data to comboBox — Konstantin Mokhov, Feb 24 '14 at 07:00

Leo · Accepted Answer · 2014-02-24T07:42:57.647

I'm not too sure what you are trying to achieve, but do you realize that you are making countless connections to the database here?...

foreach (decimal someVar in Dict.Values)
{
  OleDbCommand command = myAccessConn.CreateCommand();
  OleDbTransaction trans = myAccessConn.BeginTransaction();
  command.Transaction = trans;
  command.CommandText = "SELECT COLUMN FROM my_tb2 WHERE ROW='" + someVar + "'";
  command.ExecuteNonQuery();
  nb = Convert.ToString(command.ExecuteScalar());
  comboBox2.Items.Add(nb;
  trans.Commit();
}

Whatever the total rows returned from this query...

"SELECT COLUMN FROM my_tb WHERE ROW='" + Row + "'"

will be equivalent to the total of database connections you will be opening...not to mentioned the total amount of transactions you will open as well. Do you really need to run a transaction for this select query?

Why don't you retrieve all the dictionary values into an array...

var values = Dict.Values.ToArray();

then join the values into a CSV string....

var @param = string.Join(",", values);

and pass this @params string to an IN clause

command.CommandText = "SELECT COLUMN FROM my_tb2 WHERE ROW IN(" + @param + ")";
var reader = command.ExecuteReader();

while(reader.Read())
{
    comboBox2.Items.Add(reader["COLUMN"].ToString());
}

I'm omitting some details for clarity but if you need some clarifications, let me know

score 0 · Answer 2 · answered Feb 24 '14 at 17:04

You really need to be using Bind Variables in these kind of situations. Oracle will parse each occurance of your query as a whole new query, which will slow things down considerably. I'm not a developer so I can't tell you how to apply this to C#, but you should start by reading this helpful article on the topic:

http://www.akadia.com/services/ora_bind_variables.html

Long Query from OracleDB

2 Answers2