OleDbException System Resources Exceeded

Question

The following code executes a simple insert command. If it is called 2,000 times consecutively (to insert 2,000 rows) an OleDbException with message = "System Resources Exceeded" is thrown. Is there something else I should be doing to free up resources?

using (OleDbConnection conn = new OleDbConnection(connectionString))
using (OleDbCommand cmd = new OleDbCommand(commandText, conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
}

Answer 1

The system resources exceeded error is not coming from the managed code, its coming from you killing your database (JET?)

You are opening way to many connections, way to fast...

Some tips:

• Avoid roundtrips by not opening a new connection for every single command, and perform the inserts using a single connection.
• Ensure that database connection pooling is working (Not sure if that works with OLEDB connections).
• Consider using a more optimized way to insert the data.

Have you tried this?

using (OleDBConnection conn = new OleDBConnection(connstr))
{
    while (IHaveData)
    {
        using (OldDBCommand cmd = new OldDBCommand())
        {
            cmd.Connection = conn;
            cmd.ExecuteScalar();
        }
    }
}

Answer 2

I tested this code out with an Access 2007 database with no exceptions (I went as high as 13000 inserts).

However, what I noticed is that it is terribly slow as you are creating a connection every time. If you put the "using(connection)" outside the loop, it goes much faster.

Answer 3

In addition to the above (connecting to the database only once), I would also like to make sure you're closing and disposing of your connections. As most objects in c# are managed wrt memory, connections and streams don't have this luxury always, so if objects like this aren't disposed of, they are not guaranteed to be cleaned up. This has the added effect of leaving that connection open for the life of your program.

Also, if possible, I'd look into using Transactions. I can't tell what you're using this code for, but OleDbTransactions are useful when inserting and updating many rows in a database.

Answer 4

I am not sure about the specifics but I have ran across a similar problem. We utilize an Access database with IIS to serve our clients. We do not have very many clients but there are alot of connections being opened and closed during a single session. After about a week of work, we recieve the same error and all connection attempts fail. To correct the problem, all we had to do was restart the worker processes.

After some research, I found (of course) that Access does not perform well in this environment. Resources do not get released correctly and over time the executable will run out. To solve this problem, we are going to move to an Oracle database. If this does not fix the problem, I will keep you updated on my findings.

Answer 5

Perfect solution it is make global connection and close after 1000 insertion