性能问题: Oracle点网数组绑定

嗨，汤姆/团队，

我通过点网连接到oracle服务器Oracle数据提供程序dll Oracle.DataAccess(V 4.112.3.0)，在这里我进行了一个存储过程调用，该调用本质上使数据库插入到表，并使用序列作为out参数返回新创建的记录的id。在这里，我正在批量执行批量记录，例如通过oracle array绑定一次500记录。

实际上，我们的服务器位于纽约，我们使用ODP点网从不同的区域连接到服务器，所以如果纽约用户从同一区域连接到服务器，它是超快的，就像300ms批量执行500，当我连接到同一服务器从格拉斯哥英国，执行相同的批处理大小大约需要65秒左右，我也从不同的区域进行了检查，但是比NY花费了更多的时间。此外，我通过从存储过程中删除参数尝试了一些随机检查，当时它是超快的，就像500 ms显然不如NY区域快，因为服务器上有一些额外的网络跳。纽约人和我之间的一件事是，他将vdi机器连接到intranet，而我有一台使用cisco VPN连接到intranet的笔记本电脑。

我看了一下，深入了解数组绑定我所理解的是数组绑定创建一批记录，并在单个连接会话中一起执行它们，它的工作原理完全相同，但在out参数的情况下不起作用，我想在内部，它正在等待每次执行记录时返回某些内容，但是如果是这种情况，那么为什么它在纽约地区是超快的？以下是我的问题。
* 为什么数组绑定在两个不同的区域工作不同？
* 当我们从PL/SQL过程/函数返回某些内容时，它在内部如何工作？
* 当我们通过不同的区域 (服务器托管区域除外) 进行连接时，提高性能的最佳方法是什么？
下面是我的点网和PL/SQL代码。

C # 代码:

class Program
{
 static void Main(string[] args)
 {
  Stopwatch sw = new Stopwatch();
  OracleConnection cnn = new OracleConnection("user id=xxxx;data source=xxxx;password=xxxx");
  cnn.Open();
  try
  {
   OracleCommand cmd = cnn.CreateCommand();
   cmd.CommandText = "InsertCompanyInfoProc";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.BindByName = true;

   string[] companyNames, addresses1, addresses2, cities, states, zips, countries;
   int[] output;
   int arrSize = 500; 
   companyNames = new string[arrSize];
   addresses1 = new string[arrSize];
   addresses2 = new string[arrSize];
   cities = new string[arrSize];
   states = new string[arrSize];
   zips = new string[arrSize];
   countries = new string[arrSize];
   output = new int[arrSize];

   for (int i = 0; i < arrSize; i++)
   {
    int companyId = i+1;
    companyNames[i] = "Demo Company " + companyId;
    addresses1[i] = "123 West Washington";
    addresses2[i] = "Suite 200";
    cities[i] = "Chicago";
    states[i] = "IL";
    zips[i] = "60606";
    countries[i] = "USA";
   }

   //To use ArrayBinding, we need to set ArrayBindCount
   cmd.ArrayBindCount = arrSize;

   //Instead of single value, we pass array of values as parameters
   cmd.Parameters.Add("prm_CompanyName", OracleDbType.Varchar2, companyNames, ParameterDirection.Input);
   cmd.Parameters.Add("prm_Address1", OracleDbType.Varchar2, addresses1, ParameterDirection.Input);
   cmd.Parameters.Add("prm_Address2", OracleDbType.Varchar2, addresses2, ParameterDirection.Input);
   cmd.Parameters.Add("prm_City", OracleDbType.Varchar2, cities, ParameterDirection.Input);
   cmd.Parameters.Add("prm_State", OracleDbType.Varchar2, states, ParameterDirection.Input);
   cmd.Parameters.Add("prm_Zip", OracleDbType.Varchar2, zips, ParameterDirection.Input);
   cmd.Parameters.Add("prm_Country", OracleDbType.Varchar2, countries, ParameterDirection.Input);
   cmd.Parameters.Add("prm_CompanyId", OracleDbType.Int32, output, ParameterDirection.Output);
   
   sw.Start();
   cmd.ExecuteNonQuery();
   sw.Stop();

   Console.WriteLine("Total Elapsed Time: " + sw.ElapsedMilliseconds);
   Console.ReadKey();
  }
  catch(Exception e)
  {
   Console.WriteLine(e.Message);
  }
  finally
  {
   cnn.Close();
  }
 }
}
复制

PL/SQL代码:

CREATE OR REPLACE PROCEDURE InsertCompanyInfoProc(
 prm_CompanyName COMPANY.COMPANY_NAME%TYPE,
 prm_Address1 COMPANY.ADDRESS1%TYPE,
 prm_Address2 COMPANY.ADDRESS2%TYPE,
 prm_City COMPANY.CITY%TYPE,
 prm_State COMPANY.STATE%TYPE,
 prm_Zip COMPANY.ZIP%TYPE,
 prm_Country COMPANY.COUNTRY%TYPE,
 prm_CompanyId OUT COMPANY.COMPANY_ID%TYPE
)
IS

BEGIN
 SELECT bulk_test_seq.nextval
 INTO prm_CompanyId
 FROM DUAL;

 INSERT INTO COMPANY
 (
  COMPANY_ID,
  COMPANY_NAME,
  ADDRESS1,
  ADDRESS2,
  CITY,
  STATE,
  ZIP,
  COUNTRY
 )
 VALUES
 (
  prm_CompanyId,
  prm_CompanyName,
  prm_Address1,
  prm_Address2,
  prm_City,
  prm_State,
  prm_Zip,
  prm_Country
 );
END InsertCompanyInfoProc;


CREATE TABLE "MASTER"."COMPANY"
(
 "COMPANY_ID" NUMBER(10,0) NOT NULL ENABLE,
 "COMPANY_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
 "ADDRESS1" VARCHAR2(100 BYTE) NOT NULL ENABLE,
 "ADDRESS2" VARCHAR2(100 BYTE) NOT NULL ENABLE,
 "CITY" VARCHAR2(20 BYTE) NOT NULL ENABLE,
 "STATE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
 "ZIP" VARCHAR2(10 BYTE) NOT NULL ENABLE,
 "COUNTRY" VARCHAR2(20 BYTE) NOT NULL ENABLE,
 CONSTRAINT "COMPANY_PK" PRIMARY KEY("COMPANY_ID")
)


CREATE SEQUENCE "MASTER"."BULK_TEST_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 
 INCREMENT BY 1 
 START WITH 1 
 CACHE 20 
 NOORDER NOCYCLE NOPARTITION;
复制

我不是一个专家在点网，但我很确定你不是数组绑定这里，如果这确实是PLSQL过程，你正在调用，因为它只是采取标量参数 (不是数组)。

通常，数组绑定将使用客户端的INSERT完成，例如

cmd.CommandText =  "insert into 
  jobs (job_id, " +
        "job_title, " +
        "min_salary, " +
        "max_salary) " +
        "values (:1, :2, :3, :4)";
// set the number of elements 
// in the arrays and all three 
// arrays are the same size
cmd.ArrayBindCount = job_id_vals.Length;
// add parameters to collection
cmd.Parameters.Add(p_job_id);
cmd.Parameters.Add(p_job_title);
cmd.Parameters.Add(p_min_salary);
cmd.Parameters.Add(p_max_salary);
// perform the array insert in 
// a single call
cmd.ExecuteNonQuery();
复制

或者，您将创建PLSQL过程以接受数组，然后在PLSQL过程中 * 内部进行数组处理，例如

CREATE OR REPLACE PROCEDURE InsertCompanyInfoProc(
 prm_CompanyName varchar2_array_type
 prm_Address1 varchar2_array_type
 prm_Address2 varchar2_array_type
 prm_City varchar2_array_type
 prm_State varchar2_array_type
 prm_Zip varchar2_array_type
 prm_Country varchar2_array_type
 prm_CompanyId OUT number_array_type
)
IS

BEGIN
 forall i in 1 .. prm_CompanyName.count
   INSERT INTO COMPANY
   (
    COMPANY_ID,
    COMPANY_NAME,
    ADDRESS1,
    ADDRESS2,
    CITY,
    STATE,
    ZIP,
    COUNTRY
   )
   VALUES
   (
    bulk_test_seq.nextval,
    prm_CompanyName(i),
    prm_Address1(i),
    prm_Address2(i),
    prm_City(i),
    prm_State(i),
    prm_Zip(i),
    prm_Country(i)
   )
  returning COMPANY_ID bulk collect into prm_CompanyId;
  
END InsertCompanyInfoProc;
复制

你用填充的数组对该proc进行一次调用。

我 * 怀疑 * 这里发生的事情是我们每 * 个数组行一次调用该过程，因此您将受到巨大的延迟打击。