Hurtowe pobieranie rekordów podrzędnych z bazy Oracle

Mając chwilę czasu, spróbowałem znaleźć rozwiązanie tegoż samego problemu, co w poprzednim poście, ale już w oparciu o metody specyficzne dla serwera Oracle.

Po pobieżnej analizie linków wyszukanych na Google postanowiłem skorzystać z istniejącej w providerze Oracle.DataAccess możliwości przekazywania do procedury PLSQL tablic asocjacyjnych. Swój kod oparłem na rozwiązaniu opublikowanego w artykule Nicka N. Główną modyfikacją było przeniesienie definicji funkcji konwertującej tablice SQL i PLSQL do osobnego pakietu, tak by tego kodu nie trzeba było powielać używając w innych procedurach.

Definicja pomocniczego typu SQL:

Create Or Replace Type TableOfInts Is Table Of Int;
/

Definicja pakietu z definicją typu PLSQL i funkcją konwertującą dane z tego typu na typ SQL:

Create Or Replace PACKAGE HelpersPack AS
  TYPE AssociativeArray is table of Int Index By Binary_Integer;
  Function AssociativeArray2Tbl(p_arr In AssociativeArray) 
    Return TableOfInts;

END HelpersPack;
/

Create Or Replace Package Body HelpersPack As
    Function AssociativeArray2Tbl(p_Arr In AssociativeArray) 
    Return TableOfInts
    As
        l_Data TableOfInts := TableOfInts();
    Begin
        For i In 1..p_Arr.Last
        Loop
            l_Data.Extend;
            l_Data(l_Data.count ) := p_Arr(i);
        End Loop;
        Return l_Data;
  End;

END HelpersPack;

Sama zaś procedura ściągająca dane dla wybranych wartości klucza głównego może wyglądać tak:

Create Or Replace Procedure GetLearningOffersPrograms(
  p_OffersIds In HelpersPack.AssociativeArray,
  p_Cursor Out Sys_RefCursor)
As
  v_tmparray TableOfInts;
Begin
  v_tmpArray := HelpersPack.AssociativeArray2Tbl(p_OffersIds);

  Open p_Cursor For

    Select
      *
    From
      LearningOffersPrograms
    Where
      OfferId In (Select column_value From Table(v_tmpArray))
    Order By
      Position;
END;

A jej uruchomienie po stronie klienta .netowego:

public void LoadProgramsWithArray(DataSet data, int[] offersIds, 
    string tableName)
{
    string connectionString = System.Configuration.ConfigurationManager.
            ConnectionStrings["OracleServer"].ConnectionString;

    using (OracleConnection con = new OracleConnection(connectionString))
    {
        con.Open();
        
        OracleCommand cmd = new OracleCommand(
            "GetLearningOffersPrograms", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("p_OffersIds", OracleDbType.Int32, 
            offersIds, ParameterDirection.Input);

        cmd.Parameters.Add("p_Cursor", OracleDbType.RefCursor, 
            ParameterDirection.Output);

        OracleDataAdapter adapter = new OracleDataAdapter(cmd);
        adapter.Fill(data, tableName);
    }
}

Pobieżne testy powyższego podejścia pokazują, że wzrost wydajności w stosunku do poprzedniego rozwiązania (opartego na dynamicznym budowaniu zapytania po stronie klienta) jest relatywnie mały. Przypuszczam jednak, że w innych warunkach (np. większej liczbie rekordów) korzyści z wykonywania całego kodu po stronie serwera będą większe.