Slow performance in simple inserts

[jaredjensen]

I'm writing a migration utility that imports data into an Access database. I'm finding that even very basic inserts take nearly a second to complete.

I'm using the following TypeScript to perform inserts (logging temporarily added to debug performance):

async insert(sql: string): Promise<number> {
  try {
    console.log(sql);
    console.log(`s: ${new Date().toISOString()}`);
    const result = await this.conn.execute<LastId[]>(sql, 'SELECT @@IDENTITY as id');
    console.log(`e: ${new Date().toISOString()}`);
    if (result[0].id < 1) {
      throw new Error(`Invalid last identity result ${result[0].id}`);
    }
    return result[0].id;
  } catch (error) {
    console.error(error, sql);
    throw new Error('Error running SQL');
  }
}

Some sample output from this is:

INSERT INTO CancelReasons ([Description]) VALUES ('Defaulted');
s: 2021-08-14T06:39:42.234Z
e: 2021-08-14T06:39:43.037Z
INSERT INTO CancelReasons ([Description]) VALUES ('Medical');
s: 2021-08-14T06:39:43.039Z
e: 2021-08-14T06:39:43.852Z
INSERT INTO CancelReasons ([Description]) VALUES ('moved');
s: 2021-08-14T06:39:43.854Z
e: 2021-08-14T06:39:44.657Z

My machine is relatively fast:

• Windows 10 64-bit, 32GB RAM, SSD drive
• Node 16.6.1

Is this slow performance expected, or is there anything I'm doing wrong?

[jaredjensen]

I forgot to mention that the database is local, so the delay isn't caused by network latency.

Because of the slow performance with node-adodb, I decided to port my utility to C#. Using the test code below, I was able to insert 20 records to the same table instantly:

// DB.cs

public class DB : IDisposable
{
  static readonly string MDB_PASSWORD = "[redacted]";

  private readonly string _connString;
  private OleDbConnection _conn;

  public DB(string path)
  {
    _connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Jet OLEDB:Database Password={MDB_PASSWORD};";
  }

  public int Insert(string name)
  {
    var sql = $"INSERT INTO CancelReasons (Description) VALUES ('{name}')";
    using var cmd = new OleDbCommand(sql, _conn)
    {
      CommandType = CommandType.Text
    };
    cmd.ExecuteNonQuery();
    return GetLastIdentity();
  }

  public void Connect()
  {
    _conn = new OleDbConnection(_connString);
    _conn.Open();
  }

  public void Disconnect()
  {
    if (_conn != null)
    {
      _conn.Close();
      _conn.Dispose();
      _conn = null;
    }
  }

  public int GetLastIdentity()
  {
    using var cmd = new OleDbCommand("SELECT @@IDENTITY", _conn)
    {
      CommandType = CommandType.Text
    };
    return Convert.ToInt32(cmd.ExecuteScalar());
  }

  #region IDisposable Support
  private bool disposedValue = false;

  protected virtual void Dispose(bool disposing)
  {
    if (!disposedValue)
    {
      if (disposing)
      {
        Disconnect();
      }

      disposedValue = true;
    }
  }

  public void Dispose()
  {
    Dispose(true);
  }
  #endregion
}

// Program.cs

class Program
{
  static void Main(string[] args)
  {
    Console.WriteLine("Connecting...");
    using var mdb = new MSAccess.DB("c:/path/to/db.mdb");
    mdb.Connect();

    Console.WriteLine("Inserting...");
    Console.WriteLine($"s:{DateTime.UtcNow.ToString("s")}");
    for (var i = 1; i <= 20; i++)
    {
      var name = $"foo{i}";
      var id = mdb.Insert(name);
      Console.WriteLine($"{name}={id}");
    }
    Console.WriteLine($"e:{DateTime.UtcNow.ToString("s")}");

    mdb.Disconnect();
  }
}

This outputs the following (the database template starts with an existing entry, which is why the IDs start at 2 here):

Connecting...
Inserting...
s:2021-08-14T14:39:32
foo1=2
foo2=3
foo3=4
foo4=5
foo5=6
foo6=7
foo7=8
foo8=9
foo9=10
foo10=11
foo11=12
foo12=13
foo13=14
foo14=15
foo15=16
foo16=17
foo17=18
foo18=19
foo19=20
foo20=21
e:2021-08-14T14:39:32

At this point, I'm just going to move forward with C#. But I'm happy to provide more information or do additional testing with node-adodb if you'd like.

[imjosh]

To get around this very slow performance problem, I got edge-adodb working which calls a C# function from Node. For what I was doing with simple queries and inserts, it was basically a drop-in replacement for node-adodb

https://github.com/imjosh/edge-adodb/tree/update-2024