1 Reply Latest reply on Mar 12, 2012 4:35 PM by gdarling - oracle

    ADO UpdateBatch method very slow...

      First off, sorry if this question is in the wrong place - but here goes.

      I am using Oracle XE and for the first time I am working on an application that has what are for me very large datasets. A single update might insert 10k to 30k records. Consequently, I reasoned that using some sort of batch update would be more efficient than sending all those SQL insert statements to the database through ADO. But I was wrong...

      As a test I created a table with two fields in it ( NUMERIC(10,6) ) and inserted 10000 records into it using the UpdateBatch recordset method. It took about 50 seconds. By contrast, I inserted 10000 records into the same table using discrete SQL statements fed to the database through the connection execute method. That took about 10 seconds.

      So I did a bit of digging and found some references that the problem might be caused by the lack of a primary key in my test table, so I dropped and recreated the table adding a primary key field ( NUMERIC(28) ) and reran the tests. The UpdateBatch now takes about 35 seconds and the discrete inserts took about 11 second - a definite improvement, but still not what I was expecting. BTW, I did some troubleshooting and determined that 99% of the long update time is in the UpdateBatch Method execution, adding the records to the recordset takes a few seconds.

      The big question: Is this to be expected or am I doing something wrong? If the problem is mine where should I start looking?


      PS: I have used Oracle for some time, but this is getting into a bunch of stuff that I haven't messed with before. Also, I am quite active on a forum for another product and I understand the frustration where someone gets on and simply says: "My code is broken, how do I fix it?", so please also tell me what information you might need to answer my questions.
        • 1. Re: ADO UpdateBatch method very slow...
          gdarling - oracle
          Hi Mike,

          I don't have much in the way of a solution for you, but perhaps some observations:

          I tested the following code... in my case against a local database, so network overhead was minimal here. For the record, I'm testing 11203 oraoledb with 11202 Enterprise Edition database.
          'create table mytable(col1 number, col2 number);
          Private Sub Command1_Click()
              Dim conn As ADODB.Connection
              Dim ADOrst As ADODB.Recordset
              Set conn = New ADODB.Connection
              conn.ConnectionString = "data source=orcl;user id=scott;password=tiger;provider=oraoledb.oracle"
              conn.Execute "truncate table mytable"
              Set ADOrst = New ADODB.Recordset
              ADOrst.CursorLocation = adUseClient
              ADOrst.Open "select * from mytable", conn, adOpenStatic, adLockBatchOptimistic
              For i = 1 To 10000
              ADOrst.Fields(0).Value = 1
              ADOrst.Fields(1).Value = 2
              Next i
              startTime = Timer
              MsgBox " batch update: " & (Timer - startTime)
              startTime = Timer
              For i = 1 To 10000
              conn.Execute "insert into mytable values(1,2)"
              Next i
              MsgBox "direct inserts: " & (Timer - startTime)
              Set ADOrst = Nothing
              Set arrValues = Nothing
              Set arrRecordvals = Nothing
              Set arrFieldnames = Nothing
          End Sub
          In my case, both are approximately equal, at around 6 seconds, although the batch update performs slightly better, probably due to the use of bind variables instead of literals.

          If you enable sqlnet tracing, so you can see what is actually being sent to the database, and in this case you'll see 10,000 of the following:
          INSERT INTO "MYTABLE" ("COL1","COL2") VALUES (:1,:2) RETURNING ROWID into :3

          Given that, it seems expected that 10000 sql statements executed by you, versus 10000 sql statements executed by the provider would be approximately the same performance. I'm not sure why you see different behavior in your environment though. My only guess is that it's somehow related to the RETURNING clause. If you add that to your code, do you then see equivalent (although not good) performance?

          The thing I want to suggest though, is are you actually coding in VB(6)? Or are you using .NET? If you're using .NET, and can switch from ADO to ODP.NET, the same operation takes 65 MILLIseconds in my environment, using array binding.

          create table mytable(col1 number, col2 number);
          using System;
          using System.Data;
          using Oracle.DataAccess.Client;
          class SimpleArrayBind
              static void Main(string[] args)
                  string connectStr = "User Id=scott;Password=tiger;Data Source=orcl;";
                  int size = 10000;
                  double[] myArrayofNums1 = new double[size];
                  double[] myArrayofNums2 = new double[size];
                  for (int i = 0; i < size; i++)
                      myArrayofNums1[i] = i;
                      myArrayofNums2[i] = i/5;
                  using (OracleConnection connection = new OracleConnection(connectStr))
                      using (OracleCommand command = new OracleCommand("insert into mytable values(:1,:2)", connection))
                          command.ArrayBindCount = size;
                          OracleParameter numParam1 = new OracleParameter("p1", OracleDbType.Double);
                          numParam1.Direction = ParameterDirection.Input;
                          numParam1.Value = myArrayofNums1;
                          OracleParameter numParam2 = new OracleParameter("p2", OracleDbType.Double);
                          numParam2.Direction = ParameterDirection.Input;
                          numParam2.Value = myArrayofNums2;
                          System.DateTime starttime = System.DateTime.Now;
                          Console.WriteLine("elapsed: " + (System.DateTime.Now - starttime).Milliseconds);
          Hope it helps,