Forum Stats

  • 3,733,973 Users
  • 2,246,852 Discussions
  • 7,856,957 Comments

Discussions

Performance problems with array binding

BerndL
BerndL Member Posts: 50 Bronze Badge
edited August 2016 in ODP.NET

Hi there,

using latest fully managed ODP.NET against an Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

I have been using BulkCopy so far to transfer data from an SQLite Database to Oracle but was forced to upgrade to the fully managed provider v4.121.2.0. As an alternative to BulkCopy I use the array binding feature which works pretty well - at least most of the time.

One of the problematic tables has this definition:



CREATE TABLE AX_HistorischesFlurstueckALB(
    id VARCHAR2(16) PRIMARY KEY NOT NULL REFERENCES t_objekte_gesamt (id) ON DELETE CASCADE
  , lebenszeitintervallBeginnt TIMESTAMP WITH TIME ZONE
  , fachdatenverbindung VARCHAR2(4000)
  , fachdatenobjekt VARCHAR2(4000)
  , amtlicheFlaeche FLOAT
  , buchungBlattart CLOB
  , buchungBuchungsart CLOB
  , buchungBuchungsblattbzrkBzrk CLOB
  , buchungBuchungsblattbezirkLand CLOB
  , buchungBuchungsblattknnzchn CLOB
  , bchngBchngblnrMitBstberw CLOB
  , bchngLaufendeNrDerBchngstelle CLOB
  , flstnrNenner VARCHAR2(4000)
  , flstnrZaehler VARCHAR2(4000)
  , flurnummer VARCHAR2(4000)
  , flurstuecksfolge VARCHAR2(4000)
  , flurstueckskennzeichen VARCHAR2(4000)
  , fortfuehrungsart VARCHAR2(4000)
  , gemarkungLand VARCHAR2(4000)
  , gemarkungNummer VARCHAR2(4000)
  , laufendeNummerDerFortfuehrung VARCHAR2(4000)
  , nachfolgerFlstckskennzeichen CLOB
  , vorgaengerFlstckskennzeichen CLOB
  , zeitpktDerEntstDesBezugsflstck VARCHAR2(4000))

It contains ~26000 rows and the longest value in one of the CLOB columns is 2855 characters long.

The method that writes each of the SQLite tables to Oracle looks like this:

private void WriteTableToOracle(string tabelle, Dictionary<string, IEnumerable<object>> tabellenElemente, OracleCommand oracleCommand)
{
    _log.InfoFormat("Schreibe Daten für Tabelle {0}", tabelle);

    var sql = String.Format(@
INSERT INTO {0}
VALUES ( {1} ) , tabelle, String.Join(", ", tabellenElemente.Keys.Select(e => String.Format(":{0}", e))));

    try
    {
        oracleCommand.CommandText = sql;
        oracleCommand.Parameters.Clear();

        oracleCommand.BindByName = true;
        oracleCommand.ArrayBindCount = tabellenElemente.Values.First().Count();

        foreach (var spalte in tabellenElemente)
        {
            var parameter = new OracleParameter();
            parameter.ParameterName = spalte.Key;
            parameter.OracleDbType = GetOracleDatatypeForColumn(oracleCommand.Connection, tabelle, spalte.Key);
            parameter.Value = spalte.Value.ToArray();

            oracleCommand.Parameters.Add(parameter);
        }

        var rowsWritten = oracleCommand.ExecuteNonQuery();
        _log.InfoFormat("{0} Datensätze geschrieben", rowsWritten);
    }
    catch (OracleException e)
    {
        _log.Error("Fehler in SchreibeTabelleNachOracle():", e);
        if (e.Number == 24381)
        {
            for (int i = 0; i < e.Errors.Count; i++)
                _log.ErrorFormat("Array Bind Error {0} in Zeile {1}", e.Errors[i].Message, e.Errors[i].ArrayBindIndex);
        }

        throw;
    }
    catch (Exception e)
    {
        _log.Error("Fehler in SchreibeTabelleNachOracle():", e);
        throw;
    }
}


string tabelle is the table's name and Dictionary<string, IEnumerable<object>> tabellenElemente contains column names and their values.

oracleCommand.ExecuteNoneQuery() in line 27 returns after roughly 52 minutes , whereas other tables with >100000 rows (albeit without CLOBS and with less columns) are written in a few seconds, which is what I would expect.

Does anyone have an idea what might be causing this issue?

Thanks, Bernd

Tagged:

Answers

  • BerndL
    BerndL Member Posts: 50 Bronze Badge
    edited June 2016

    I'm not a DB admin, so not sure what that means, but I've been watching the sessions report in SQL Developer during the insert, and even half an hour after the table has been written I can still see

    INSERT

    INTO

      AX_HistorischesFlurstueckALB VALUES

      (

        :id,

        :lebenszeitintervallBeginnt,

        :fachdatenverbindung,

        :fachdatenobjekt,

        :amtlicheFlaeche,

        :buchungBlattart,

        :buchungBuchungsart,

        :buchungBuchungsblattbzrkBzrk,

        :buchungBuchungsblattbezirkLand,

        :buchungBuchungsblattknnzchn,

        :bchngBchngblnrMitBstberw,

        :bchngLaufendeNrDerBchngstelle,

        :flstnrNenner,

        :flstnrZaehler,

        :flurnummer,

        :flurstuecksfolge,

        :flurstueckskennzeichen,

        :fortfuehrungsart,

        :gemarkungLand,

        :gemarkungNummer,

        :laufendeNummerDerFortfuehrung,

        :nachfolgerFlstckskennzeichen,

        :vorgaengerFlstckskennzeichen,

        :zeitpktDerEntstDesBezugsflstck

      )

    under "Active SQL". "Waits" tab shows "SQL*Net more data from client", whatever that means.

    In the meantime my application logfile says that another table is already being written .

  • smerkli
    smerkli Member Posts: 7
    edited August 2016

    I have had that problem, too way back with an unmanaged ODP.NET version. The problem was the BLOB (probably all LOB types) in the table. Inserts using ArrayBind on Tables having such columns did degrade exponentially with the number of rows. Here are the measurements results I got:

       

    NumRowsInsert Duration [seconds][ms] Per Insert
    1003.434
    2007.638
    3001756.66666667
    4003792.5
    50071142
    600122203.3333333

    I did not get ORACLE to fix this and worked around the problem as we do not often use LOBs in our tables.

    Regards

    Rolf

This discussion has been closed.