Hi Tom / Team,

I am connecting to the oracle server through dot net Oracle Data Provider dll Oracle.DataAccess(V 4.112.3.0) where I make a stored procedure call which essentially makes a database insert to a table and returns the id of newly created record using the sequence as out parameter. Here I am making batch execution of bulk records e.g. 500 records at a time through oracle array binding.

Actually, our server is sitting in Newyork and we connect to the server from different region using ODP dot net, so if NY user connects to server from the same region it is superfast like 300ms for batch execution of 500 and when I connect to the same server from Glasgow UK, it takes around 65 sec or so to execute same batch size, I checked from the different region as well but it took more time than NY. Additionally, I tried some random checks by removing out parameter from the stored procedure, At that time it was superfast like 500 ms obviously not as fast as the NY region as there are some additional network hops to the server. Here one thing that is different between the NY guy and me is he has vdi machine wired to the intranet and I have a laptop connecting to the intranet using cisco VPN.

I took a look to understand Array Binding in depth what I understood is Array Binding creates a batch of records and execute them together within a single connection session, it works exactly the same but not in the case of out parameter, I guess internally it is waiting for something to return on each record execution but if this is the case then why it is superfast in NY region? Below are my questions.
* Why array binding works differently in two different regions?
* How it works internally when we return something from PL/SQL procedure/function?
* What is the best way to improve performance when we connect through the different regions(other than server hosting region)?
below are my dot net and PL/SQL code.

C# Code :

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 Code :

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;

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

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;