CODEWITHSUNDEEP
c#.net-coreodbc
kenyu73
kenyu73

Reputation: 681

ODBCDataReader very slow

The following basic code takes about 35 seconds to complete the dt.load(reader). The database is a MS Access .mdb database with about 210 "columns" and maybe 37 rows.

I've seen many blogs and posts about this where "its slow" "is there another way" etc etc. This is only 200 or so rows... maybe 1 sec tops, right?

string connectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=" + file;
_conn = new OdbcConnection(connectionString);

string sql = "select * from fields";

OdbcCommand command = new OdbcCommand(sql, _conn);
OdbcDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable();

dt.Load(reader); // 30+ seconds to complete

foreach (DataRow row in dt.Rows)
{
    ...

Also tried the below... same 35 seconds delay on Fill()

        OdbcDataAdapter adapter = new OdbcDataAdapter();
        DataSet ds = new DataSet();
        ds.Clear();

        adapter.SelectCommand = command;
        adapter.Fill(ds);

Upvotes: 0

Views: 1274

Answers (3)

kenyu73
kenyu73

Reputation: 681

Found this and it corrected my issue! Amazing results now... 327 milliseconds including overhead for the EF.core method calls in the same call (destination connection).

https://answers.microsoft.com/en-us/office/forum/office_2010-access/my-solution-to-access-being-slow-with-odbc/a5a6522f-a70f-421e-af1b-48327075e010

enter image description here

Upvotes: 2

Cahaba Data
Cahaba Data

Reputation: 622

Access is a front end database, meaning that tables are essentially passive - little different than a csv file in a sense.

Access does have a 255 field max so at 210 you are approaching that - and while perhaps that is an issue, you could test a lower field count to be sure - but I doubt that it is. Record set queries run fast. Running a select query within Access for this table would execute in the blink of an eye - - but you are setting up a connection outside of Access it appears - so where does this code sit.. that is invoking the ODBC reader? Would this same connection code be faster if aimed at a different data set format other than an Access mdb? ….

Upvotes: 1

Auction God
Auction God

Reputation: 109

Access is much slower when multiple users are accessing the database. Make sure your app isn't connected and check for a .ldb file in the access directory. If it is there, someone else has it open (or crashed out, leaving locks). If so, try the compact and repair option in the menu. (By default it runs this when the last person exits the database, but if someone crashes out the database never gets compacted).

Upvotes: 1

Related Questions