Open jaredjensen opened 3 years ago
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.
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
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):
Some sample output from this is:
My machine is relatively fast:
Is this slow performance expected, or is there anything I'm doing wrong?