Really Slow Performance Reading from Access 2007 (accdb) file from C#

Question

I am writing an application on Visual Studio 2008 with C#. The application reads data from an access file and then generates a txt file. I was doing some tests with a mdb file with 1.000.000 records and almost 1GB size. The code is like this and the overall process was taking between 8 - 10 minutes to complete.

var connStr =  string.Format("Provider =Microsoft.Jet.OLEDB.4.0; Data Source={0};Persist Security Info=False", this.dbPath);

using (var conn = new OleDbConnection(connStr))
{
            using (var command = conn.CreateCommand())
            {   

                command.CommandText = "SELECT * from Registros r, FOIDS f where r.TICKET = f.TICKET";
                command.CommandType = System.Data.CommandType.Text;
                conn.Open();

                int i = 0;
                string ticket = string.Empty;

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                         i++;

                            if (!reader.IsDBNull(reader.GetOrdinal("r.TICKET")))
                            {
                                ticket=reader.GetString(reader.GetOrdinal("r.TICKET"));
                                // Some process
                            }
                        }
                    }
                }
          }
    }

Today I received an accdb file (Access 2007), so I've changed my connection string to this one:

connStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False", this.dbPath);

But after that change, reading the new file is taking about 4-5 seconds per record !!! So, my overall process would take a lot of DAYS to complete! I've tried converting the accdb file to an old mdb file and after that reading again with the previous connection string, but the problem remains. I think is a problem with the database itself but I don't know what to do, in internet i didn't found any information with this kind of problem.

Any ideas? Suggestions?

Flourishing

Is it possible that its not the reading of the MDB that is taking to much time but rather the "//some process" portion? Is it something that you could make Async? – Fast

I did some debugging and the delay is on the reader.Read() instruction. Also I've added some logs with the same result and also I've commented the process with same results. Thanks for your quick response ! – Flourishing

have you tried compacting the database? – Skiascope

any possibility to move the database to SQL instead? – Fast

@MitchWheat, yes, but compacting the DB didn't fix the problem. – Flourishing

@Matt, yes I am looking for something like this. But It's strange, I converted my original mdb database to accdb and it works perfectly. The data for both databases looks like very similar....should be an option or something that is causing the problem...Thanks! – Flourishing

What happens if you attempt to load all the data into memory, say using OleDbDataAdapter.Fill(DataTable), and then processing that in-memory set of rows?? Does it run any faster? It's hard to really say without access to a copy of a at least a dummy database with the same behavior. – Kidnap

Having the TICKET columns indexed is essential. – Turnpike

You may wish to look at msdn.microsoft.com/en-us/library/aa188211%28office.10%29.aspx, in particular Microsoft's Unsupported Jet Optimization Tools. The key for ACE is shown in #2735912 – Unbuild

Answer 1

In my experience, 5 years ago.

One of the access database exceeded 20000 records and the size was around 150 MB. It started to slow down and performance plummeted. So moved to SQL.

You may consider using SQL compact edition, or sql lite

thanks

Toniatonic

Check out: #482219 – Mesoderm

150MBs is ridiculously small for Access/Jet/ACE. If that's causing a performance problem, then you've got a bad design, either in your data schema or in your applicaiton. In the present instance, with a 1GB file, I would, however, recommend upsizing to a server database, as it's just way to close to the 2GB hard limit for an Access/Jet/ACE file. – Endor

If such a small number of records resulted in bad performance, I would first suspect Cartesian joins, and second a lack of indexes, or perhaps both. Other DB systems can optimize Cartesian joins, but IIRC Jet does not (or did not then). – Lithographer

Answer 2

In addition to switching to one of the suggested databases, you should change the object type of ticket from string to StringBuilder.

Remember that...

The String object is immutable. Every time you use one of the methods in the System.String class, you create a new string object in memory, which requires a new allocation of space for that new object. In situations where you need to perform repeated modifications to a string, the overhead associated with creating a new String object can be costly. The System.Text.StringBuilder class can be used when you want to modify a string without creating a new object. For example, using the StringBuilder class can boost performance when concatenating many strings together in a loop.

With that in mind, consider the overhead that you are creating in your while loop each time you set your ticket variable. - You mentioned that you have 1 million records, which means that your code is creating 1 million string objects.

Using a StringBuilder type in place of a string type, your code may look like this...

....
StringBuilder ticket = new StringBuilder();

using (var reader = command.ExecuteReader())
{
    while (reader.Read())
    {
         i++;

            if (!reader.IsDBNull(reader.GetOrdinal("r.TICKET")))
            {
                ticket.Append(reader.GetString(reader.GetOrdinal("r.TICKET")));
                // Some process
            }
        }
    }

    // Write the ticket content to a file
    using(StreamWriter sw = new StreamWriter("ticket.txt"))
    {
        sw.WriteLine(ticket.ToString());
    }
}

Recommit

This assumes that the ticket variable is accumulating data with each loop iteration. The question seems to indicate otherwise. – Lithographer

Answer 3

My experience converting data structure FROM MDB TO ACCDB file format is:

1. After creating a new accdb file with exactly the same objetcs and data and after compacting it the accdb increases almost 40% file size extra than the old mdb file.

2. It is slower. Data process using Query and/or VB code takes AT LEAST 3x more time to reach the end.

3. It reaches 2Gb (database size limit) faster than the previous format reaches 1GB.

And I realize that when you compact an accdb file it first generates a mdb file and after rename it for accdb extension. This is really awful!

Lyall