Dataset, SqlDataAdapter, memory and you in .net 2

[Guest]

hey doods and doodettes.

I am having severe memory issues when opening Datasets in .net 2.0. A
web-based app will cause the worker process to recycle on a heavy-duty server
after just a few client connections, and a windows app will bring the machine
to a crawl. My testing code is as follows. It is used as the source for a
report that can obviously be run many times. Even if I isolate the code to
just opening and closing the data (as shown below), major problems still
occur. Have I left anything out?

using (SqlDataAdapter TheAdapter = new SqlDataAdapter("select * from
customermaster", DoodConnection))
{
using (DataSet TheCustomers = new DataSet())
{
TheCustomers.Tables.Add("CustomerMaster");
TheAdapter.Fill(TheCustomers, "CustomerMaster");
TheAdapter.Dispose();
TheCustomers.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}

The "using" was added based on a post I found but did not help at all.
Adding calls to GC helped a bit, but in the long run this is ridiculous!!!

 

[Marina Levit [MVP]]

The 'using' statement automtically disposes all the objects when the
statement is finished. So you don't need to duplicate that. The suggestion
was to use the using statement *instead* of calling Dispose.

The main problem I think is that you are calling the GC yourself - why? I
think you should just let it do its job when it feels it is necessary.

How many rows of data does 'customermaster' have? How many columns?

 

[Guest]

The main problem I think is that you are calling the GC yourself - why?
Wrong. That is NOT the main problem. If you noticed, I said that adding
the calls to GC actually helped a bit.

How many rows of data does 'customermaster' have? How many columns?

This is a large Dataset, but that should not be the focus of the problem.
Memory should not be sucked up by subsequent requests to the dataset. When
the set is disposed, it should release the memory or at least reuse it.
Instead, each new request grabs more memory.

 

[Marina Levit [MVP]]

I think it is the main problem because you shouldn't be getting enormous
amounts of data from the database, and that you should *not* be manually
invoking the GC and having it run constantly! The large dataset is exactly
what is the focus of this problem.

Memory is not reclaimed immediately just because an object is disposed. In
fact, calling Dispose is just done to clean up the object and have it
release all of its unmanaged resources. It is not done to 'reclaim' memory
somehow immediately. The object will stay in memory until the GC decides it
needs to go clean up and get more memory.

It sounds like your dataset is enormous, and the GC can't run fast enough to
reclaim memory before it is needed again.

I think you have mistaken the dataset for a in memory database that needs to
hold a ton of data. You should only get the data you need from the database
when you need it.

Change your code to retrieve only 5 rows of data, remove the calls to GC and
all your memory problems will go away.

 

[Guest]

The addition of GC statements helped. Are you disputing that?

 

[Marina Levit [MVP]]

I'm not disputing that, I haven't even run this code.

I am pointing out a problem with the premise of the code to begin with. I
also tried to give an explanation of why it's not working as you expect and
not solving all your memory problems.

If you are not interested in suggestions and advice, then I'm not sure I
understand why you posted here to begin with.

 

[Guest]

I AM interested in the suggestions. However if you are saying the only answer
to the problem is paring down the dataset, then that is unacceptable. I am
not mistaking the dataset for an "in-memory database", I am using it for one
of the many things it is meant for - a source for a report. The fact is the
memory is never reclaimed and everytime you create the dataset, more memory
is sucked up and retained. Forever. So what can I do to release that
memory? I do not have this problem in earlier versions of .net.

 

[Marina Levit [MVP]]

If you have identical code, that in 1.1 does not cause any problems, but in
2.0 brings the server down, I would report it as a problem to Microsoft.

Since you say how many rows you were retrieving, despite me asking, and you
were doing a 'select *', my suggestion was to get the data on a need by need
basis. Meaning one page of a report at a time, one 'customer' at a time,
etc. Since I obviously have no way of knowing exactly what you are trying to
do, I was just taking a guess at what is the most likely think you were
doing. If you don't want people guessing, then you may want to provide more
information or answer their follow up questions.

 

[Guest]

Maybe we're not on the same page. I didn't think I left anything up for
guesswork. I just wanted to know how to reclaim or reuse the dang memory
that is sucked away by subsequent creations of a particular dataset. Getting
the customers one at a time would not work efficiently in this reporting
paradigm that is based on Crystal Reports. I do appreciate your time,
Marina. And by the way, you are hot!

 

[Julie Lerman]

1) what about your SqlConnection. Are you closing that? That's where the
using statement will benefit you.

2) "you are hot" - are you kidding me? What is this, 1950?

 

[Cor Ligthert [MVP]]

The chad,

And by the way, you are hot!

I was busy with an answer. However this extends any limit I have seen on
these newsgroups.

Marina is a professional who tries to help other people.

For everybody active in these pages is your text and code with what you
started a cruel.

Therefore she was helping you in the right way, instead of giving a direct
solution. Your code shows that you are probably a beginner or somebody who
has learned something in the past century and want to keep his same way of
doing things.

As she expressed, if you don't want help, than don't ask for it only to tell
to others that you did that.

Cor

 

[Marina Levit [MVP]]

I'll say, especially considering he's never seen me!

 

[Marina Levit [MVP]]

Thanks Cor
Oy...

The chad,


I was busy with an answer. However this extends any limit I have seen on
these newsgroups.

Marina is a professional who tries to help other people.

For everybody active in these pages is your text and code with what you
started a cruel.

Therefore she was helping you in the right way, instead of giving a direct
solution. Your code shows that you are probably a beginner or somebody who
has learned something in the past century and want to keep his same way of
doing things.

As she expressed, if you don't want help, than don't ask for it only to
tell to others that you did that.

Cor

 

[Guest]

1) You guys and girls need to lighten up. Seriously.

2) "You are hot" was only a GUESS. Especially since Marina's picture is
one of the few not on the vb.net MVP page. I was just trying to introduce
some sarcasm into the discussion (based on statements Marina made about
guessing), but it fell on deaf ears.

3) "Your code shows that you are probably a beginner. . ." Of course the
code was rudimentary; I clearly stated this was test code. I also asked if I
had left anything out. And yes, I have just started to use .net 2.0, which
is why I posted here. However, I have successfully completed some major
data-driven .net projects since 2001 that have made the lives of thousands of
people much easier, so I would hardly call myself a beginner.

4) ". . . don't ask for [help] only to tell to others that you did that."
Well, what are we here for? I thought it was troubleshooting and analysis.
Should I just stop the thread once someone gives me a suggestion, even if
that suggestion has already been tested or will not work for the situation?

5) I do want help. Really. I just want to know why a large dataset-one
that's just used for an instant-can consume and retain so much memory. By
retain I mean for the life of the application. Of course when the dataset is
smaller the problem is not as evident, but it IS still there.

6) I was not being sarcastic about appreciating any time spent on the
reflection of my problem. I do appreciate all of you. Now, does anyone have
any advice??

 

[Julie Lerman]

1) from my previous reply: " what about your SqlConnection. Are you closing
that? That's where the
using statement will benefit you."
2) " I was just trying to introduce some sarcasm into the discussion "
Baloney. It was beyond inappropriate - but really just plain stupid.

 

[Guest]

what about your SqlConnection. Are you closing that?

The connection string is sent directly to the adapter via the constructor.
I failed to make it clear that my inappropriately named variable
"DoodConnection" was a string.

Baloney. It was beyond inappropriate - but really just plain stupid. {that's a little harsh don't you think?}

Upon further reflection, the statement was over the top. It was probably on
the verge of sexism, and for that I apologize. I am a professional, but I
also like to have fun with people. Sometimes, however, that fun is lost in
textual conversations. I truely meant no harm-I just misjudged my audience's
sense of humour and this medium's ability to transport it.

 

[Cor Ligthert [MVP]]

The chad,

1) You guys and girls need to lighten up. Seriously.

Somebody with a normal intelligence would have been so clever that he had
made an excuse to Marina and not a sentence like this if he needed help.

Cor

 

[Julie Lerman]

but are you closing the connection? If you aren't, it would definitely be a
reason for your memory problems.

Hopefully Marina will read the rest...

 

[Guest]

but are you closing the connection?
You mean TheAdapter.Dispose() does not take care of this? Do you have to
close the connection for every type of command (select, update, delete) you
have created with the adapter? Well, I went ahead and tried this but it did
not help.

Hopefully Marina will read the rest...

Cor, too, I suppose. Thanks!!

 

[Marina Levit [MVP]]

Ok, I appreciate the apology. I do find it interesting that you went to look
my MVP profile up to either confirm or deny my hotness...

In any case, disposing the adapter does not do anything to the connection it
is using. Multiple adapters/commands could be referencing the same
connection, and so any one of them wouldn't close a connection when it is
getting dispose. Otherwise, an adapter that is out of scope that is getting
GC'ed, would close a connection in its Dispose - but this connection might
still be in scope and used elsewhere, and so this would be highly
undesirable.

The Fill method will open the connection if closed. And if the connection
was closed when it started, it will close it once its done. If the
connection was open to begin with, Fill will leave it alone.

You are sending just the connection string, so that should manage the
connection for you.

I don't think you are having memory problems due to connection leaking -
that would end up being a pooling issue. My best guess is that the GC is
thrashing with trying to allocate huge chunks of memory, and then not being
able to reclaim it fast enough. You still haven't told us how many rows your
dataset ends up holding with how many columns in each row, so it's just a
guess.