Memory leak in OleDbCommand.ExecuteNonQuery()?

1 Apr 2005 3:55 PM

Colin Sewell

Maybe there's something I'm doing wrong here. I'm open to any
suggestions.

I'll see if I can explain this clearly...

I have a C# application that runs 24x7 collecting data from some
source and storing the data in a Microsoft Access database. The
problem is, every time it stores the data using a call to
OleDbCommand.ExecuteNonQuery(), the applications memory usage grows.
After a few days, the application has consumed most of the systems'
virtual memory and grows more and more sluggish and unresponsive. The
only solution right now is to restart the application every couple of
days.

Since the app uses one database and stores the same database fields
with only the data changing, I set up everything at the start of the
program. After that, as data comes in, only the stored data parameter
values change.

The first thing I do is open the MS Access database using an
OleDbConnection with a Microsoft.Jet.OLEDB.4.0 provider. I then create
a OleDbCommand with a command string that basically consists of
"INSERT INTO table (field1, field2...) VALUES (?,?...)". Next I use
OleDbCommand.Parameters.Add to add OleDbParameter data parameters that
correspond to each field to the OleDbCommand, with the proper datatype
for each field. Lastly I set the OleDbConnection in the OleDbCommand.

As the data is collected I go through each OleDbParameter and set its
Value to either the data collected or DBNull.Value if the data is
missing. Then I call OleDbCommand.ExecuteNonQuery to actually store
the data. That occurs successfully, but the virtual memory alllocation
goes up and up each time the data is stored. I monitor the memory
usage with Sysinternals ProcExp, and if I comment out the call to
actually store the data, the memory usage remains constant.

So am I doing something wrong, or is there a memory leak in
OleDbCommand.ExecuteNonQuery?

1 Apr 2005 4:20 PM

Alex Passos

Are you also removing the parameters from your parameter collection? Like

for(int i = 0; i < dbCommand.Parameters.Count; i++) {
dbCommand.Parameters.Remove(0);
}

When I use the command object I add the number of parameters via Add, then
execute, then have equally a number of Remove(0) for each parameter.

Alex

Show quote

"Colin Sewell" <csew***@telus.net> wrote in message
news:0lrq41d0mtl71uccpo59nd56t4cffmkgnm@4ax.com...
> Maybe there's something I'm doing wrong here. I'm open to any
> suggestions.
>
> I'll see if I can explain this clearly...
>
> I have a C# application that runs 24x7 collecting data from some
> source and storing the data in a Microsoft Access database. The
> problem is, every time it stores the data using a call to
> OleDbCommand.ExecuteNonQuery(), the applications memory usage grows.
> After a few days, the application has consumed most of the systems'
> virtual memory and grows more and more sluggish and unresponsive. The
> only solution right now is to restart the application every couple of
> days.
>
> Since the app uses one database and stores the same database fields
> with only the data changing, I set up everything at the start of the
> program. After that, as data comes in, only the stored data parameter
> values change.
>
> The first thing I do is open the MS Access database using an
> OleDbConnection with a Microsoft.Jet.OLEDB.4.0 provider. I then create
> a OleDbCommand with a command string that basically consists of
> "INSERT INTO table (field1, field2...) VALUES (?,?...)". Next I use
> OleDbCommand.Parameters.Add to add OleDbParameter data parameters that
> correspond to each field to the OleDbCommand, with the proper datatype
> for each field. Lastly I set the OleDbConnection in the OleDbCommand.
>
> As the data is collected I go through each OleDbParameter and set its
> Value to either the data collected or DBNull.Value if the data is
> missing. Then I call OleDbCommand.ExecuteNonQuery to actually store
> the data. That occurs successfully, but the virtual memory alllocation
> goes up and up each time the data is stored. I monitor the memory
> usage with Sysinternals ProcExp, and if I comment out the call to
> actually store the data, the memory usage remains constant.
>
> So am I doing something wrong, or is there a memory leak in
> OleDbCommand.ExecuteNonQuery?
>

1 Apr 2005 5:14 PM

Colin Sewell

"Alex Passos" <bz@netmerlin.nospam.com> wrote:

>Are you also removing the parameters from your parameter collection? Like
>
>for(int i = 0; i < dbCommand.Parameters.Count; i++) {
> dbCommand.Parameters.Remove(0);
>}
>
>When I use the command object I add the number of parameters via Add, then
>execute, then have equally a number of Remove(0) for each parameter.

Well, no, because I would just have to add them again the next time I
have to store some data. The parameters remain the same, only their
values change.

1 Apr 2005 5:30 PM

Alex Passos

This may be applicable to you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;248014

Show quote

"Colin Sewell" <csew***@telus.net> wrote in message
news:780r41lfs6bemk3s3kljjltmaqlkia6co9@4ax.com...
> "Alex Passos" <bz@netmerlin.nospam.com> wrote:
>
>>Are you also removing the parameters from your parameter collection? Like
>>
>>for(int i = 0; i < dbCommand.Parameters.Count; i++) {
>> dbCommand.Parameters.Remove(0);
>>}
>>
>>When I use the command object I add the number of parameters via Add, then
>>execute, then have equally a number of Remove(0) for each parameter.
>
> Well, no, because I would just have to add them again the next time I
> have to store some data. The parameters remain the same, only their
> values change.

1 Apr 2005 6:08 PM

Colin Sewell

"Alex Passos" <bz@netmerlin.nospam.com> wrote:

>This may be applicable to you:
>
>http://support.microsoft.com/default.aspx?scid=kb;en-us;248014
>

OK, this looks promising. I'd rather not have to edit the registry at
the customers' site, but I am having a little problem getting the
parameter set in the OledbConnection string:

con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Jet
OLEDB:Max Buffer Size=1024;Data Source=database.mdb" );

fails with the message: "Could not find installable ISAM."

1 Apr 2005 6:50 PM

Colin Sewell

Colin Sewell <csew***@telus.net> wrote:

Show quote

>"Alex Passos" <bz@netmerlin.nospam.com> wrote:
>
>>This may be applicable to you:
>>
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;248014
>>
>
>OK, this looks promising. I'd rather not have to edit the registry at
>the customers' site, but I am having a little problem getting the
>parameter set in the OledbConnection string:
>
> con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Jet
>OLEDB:Max Buffer Size=1024;Data Source=database.mdb" );
>
>fails with the message: "Could not find installable ISAM."
>

I've edited my registry and the solution indeed seems to be working.
According to this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q318161

you can't set Session-Level Jet Properties in the OledbConnection.
There doesn't seem to be any way of solving this short of a registry
edit.

1 Apr 2005 8:16 PM

Alex Passos

Try it without the Jet OLEDB portion just set the Max Buffer size, that
would be my next guess.

Show quote

"Colin Sewell" <csew***@telus.net> wrote in message
news:s03r41hmgg0nh0ht49vq4od6jrmkv849rr@4ax.com...
> "Alex Passos" <bz@netmerlin.nospam.com> wrote:
>
>>This may be applicable to you:
>>
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;248014
>>
>
> OK, this looks promising. I'd rather not have to edit the registry at
> the customers' site, but I am having a little problem getting the
> parameter set in the OledbConnection string:
>
> con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Jet
> OLEDB:Max Buffer Size=1024;Data Source=database.mdb" );
>
> fails with the message: "Could not find installable ISAM."
>
>

1 Apr 2005 4:22 PM

Marina

We can't say if you are doing something wrong since you didn't provide any
relevant code.

Show quote

"Colin Sewell" <csew***@telus.net> wrote in message
news:0lrq41d0mtl71uccpo59nd56t4cffmkgnm@4ax.com...
> Maybe there's something I'm doing wrong here. I'm open to any
> suggestions.
>
> I'll see if I can explain this clearly...
>
> I have a C# application that runs 24x7 collecting data from some
> source and storing the data in a Microsoft Access database. The
> problem is, every time it stores the data using a call to
> OleDbCommand.ExecuteNonQuery(), the applications memory usage grows.
> After a few days, the application has consumed most of the systems'
> virtual memory and grows more and more sluggish and unresponsive. The
> only solution right now is to restart the application every couple of
> days.
>
> Since the app uses one database and stores the same database fields
> with only the data changing, I set up everything at the start of the
> program. After that, as data comes in, only the stored data parameter
> values change.
>
> The first thing I do is open the MS Access database using an
> OleDbConnection with a Microsoft.Jet.OLEDB.4.0 provider. I then create
> a OleDbCommand with a command string that basically consists of
> "INSERT INTO table (field1, field2...) VALUES (?,?...)". Next I use
> OleDbCommand.Parameters.Add to add OleDbParameter data parameters that
> correspond to each field to the OleDbCommand, with the proper datatype
> for each field. Lastly I set the OleDbConnection in the OleDbCommand.
>
> As the data is collected I go through each OleDbParameter and set its
> Value to either the data collected or DBNull.Value if the data is
> missing. Then I call OleDbCommand.ExecuteNonQuery to actually store
> the data. That occurs successfully, but the virtual memory alllocation
> goes up and up each time the data is stored. I monitor the memory
> usage with Sysinternals ProcExp, and if I comment out the call to
> actually store the data, the memory usage remains constant.
>
> So am I doing something wrong, or is there a memory leak in
> OleDbCommand.ExecuteNonQuery?
>

1 Apr 2005 5:38 PM

Colin Sewell

"Marina" <someone@nospam.com> wrote:

>We can't say if you are doing something wrong since you didn't provide any
>relevant code.

OK, I've tried to pare the code down to the essentials. minus all the
try catch blocks...

This is the setup code and gets executed once:
----------------------------------------------------------------------
private OleDbConnection con = null;

private const string    SQLIns = @"INSERT INTO table
(Field1,Field2,Field3,[Field4],Field5,Field6) VALUES (?,?,?,?,?,?)";

private OleDbCommand    cmdIns = new OleDbCommand( SQLIns );

private OleDbParameter[] parIns    = new OleDbParameter[]
    { new OleDbParameter( "@Field1", OleDbType.DBTimeStamp ),
      new OleDbParameter( "@Field2", OleDbType.VarWChar ),
      new OleDbParameter( "@Field3", OleDbType.VarWChar ),
      new OleDbParameter( "@Field4", OleDbType.VarWChar ),
      new OleDbParameter( "@Field5", OleDbType.VarWChar ),
      new OleDbParameter( "@Field6", OleDbType.DBTimeStamp ) };

    for (int I = 0; I <= parIns.GetUpperBound(0); I++)
        cmdIns.Parameters.Add( parIns[I] );

    con = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + databasename );
    con.Open();
    cmdIns.Connection = con;
----------------------------------------------------------------------

Each time some new data comes in, it's passed to a routine as a ref
array of strings called Data[] corresponding to the parameters:
----------------------------------------------------------------------
    for (int I = 0; I <= Data.GetUpperBound(0); I++)
        if (Data[I] != null)
            parIns[I].Value = Data[I];
    else
            parIns[I].Value = DBNull.Value;

    cmdIns.ExecuteNonQuery();
----------------------------------------------------------------------

The above routine gets called repeatedly. If I comment out the
cmdIns.ExecuteNonQuery() statement, memory requirements reach a peak
and stay there. Otherwise, every call to cmdIns.ExecuteNonQuery()
consumes a bit more memory until virtual memory is exhausted.

1 Apr 2005 5:10 PM

Mark Rae

"Colin Sewell" <csew***@telus.net> wrote in message
news:0lrq41d0mtl71uccpo59nd56t4cffmkgnm@4ax.com...

> So am I doing something wrong, or is there a memory leak in
> OleDbCommand.ExecuteNonQuery?

I can't see (anything wrong with) your code... :-)

3 Apr 2005 4:23 AM

Stephany Young

So, your app runs 24/7 and collects data from some source.

What you don't say is how often the incoming data 'appears'. e.g. Is it many
times per second, many times per minute, many times per hour, occassionally,
etc. Does it appear in bursts - a number within a short timespan and then no
more for a significant time.

If it 'appears' one sample at a time with a significant timespan between
samples then you could consider opening and closing the connection for each
sample.

If samples 'appear' in a burst with a significant timespan between bursts
then you could consider opening and closing the connection for each burst.

In general you could consider closing and reopening the connection after x
number of samples on a regular basis or after a regular timespan.

Remember that the article states that the 'problem' is per connection so
closing and reopening the connection regularly should work around the issue
without needing to modify the target machines registry.

Show quote

"Colin Sewell" <csew***@telus.net> wrote in message
news:0lrq41d0mtl71uccpo59nd56t4cffmkgnm@4ax.com...
> Maybe there's something I'm doing wrong here. I'm open to any
> suggestions.
>
> I'll see if I can explain this clearly...
>
> I have a C# application that runs 24x7 collecting data from some
> source and storing the data in a Microsoft Access database. The
> problem is, every time it stores the data using a call to
> OleDbCommand.ExecuteNonQuery(), the applications memory usage grows.
> After a few days, the application has consumed most of the systems'
> virtual memory and grows more and more sluggish and unresponsive. The
> only solution right now is to restart the application every couple of
> days.
>
> Since the app uses one database and stores the same database fields
> with only the data changing, I set up everything at the start of the
> program. After that, as data comes in, only the stored data parameter
> values change.
>
> The first thing I do is open the MS Access database using an
> OleDbConnection with a Microsoft.Jet.OLEDB.4.0 provider. I then create
> a OleDbCommand with a command string that basically consists of
> "INSERT INTO table (field1, field2...) VALUES (?,?...)". Next I use
> OleDbCommand.Parameters.Add to add OleDbParameter data parameters that
> correspond to each field to the OleDbCommand, with the proper datatype
> for each field. Lastly I set the OleDbConnection in the OleDbCommand.
>
> As the data is collected I go through each OleDbParameter and set its
> Value to either the data collected or DBNull.Value if the data is
> missing. Then I call OleDbCommand.ExecuteNonQuery to actually store
> the data. That occurs successfully, but the virtual memory alllocation
> goes up and up each time the data is stored. I monitor the memory
> usage with Sysinternals ProcExp, and if I comment out the call to
> actually store the data, the memory usage remains constant.
>
> So am I doing something wrong, or is there a memory leak in
> OleDbCommand.ExecuteNonQuery?
>