CODEWITHSUNDEEP
c#performancems-accessms-officeoledb
user972331
user972331

Reputation: 51

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

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?

Upvotes: 5

Views: 4111

Answers (3)

Hendel
Hendel

Reputation: 21

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!

Upvotes: 2

Jed
Jed

Reputation: 10887

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());
    }
}

Upvotes: 2

carrieat
carrieat

Reputation: 92

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

Upvotes: 5

Related Questions