c# - 来自 OracleDB 的长查询

Question

我已经创建了一个到 oracle 数据库的查询

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();
}

它可以正常工作，但需要很长时间才能执行，而且我的函数中有很多查询。如何更改代码以减少请求时间？

Answer 1

我不太确定你想要达到什么目的，但你是否意识到你正在与这里的数据库建立无数连接？...

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();
}

无论此查询返回的总行数是多少...

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

将等于您将打开的数据库连接总数……更不用说您将打开的交易总量。您真的需要为此选择查询运行事务吗？

为什么不将所有字典值检索到一个数组中...

var values = Dict.Values.ToArray();

然后将这些值加入一个 CSV 字符串....

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

并将此@params 字符串传递给IN 子句

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

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

为了清楚起见，我省略了一些细节，但如果您需要一些说明，请告诉我