Dataset, SqlDataAdapter, memory and you in .net 2

J

Julie Lerman

hmmm - just remembering that adapter.fill opens and closes the connection
for you. :-(

so I'm lookingmore closely at your code

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

It's very unkosher. First of all, you do not have to call dispose on the
adapter or the dataset. They are managed objects. The issues are around the
connection itself - which is calling unmanaged resources.This is why you
need to be sure to close connections. Again adapeter.fill will implicitly
open and close a connection. The adapter.dispose has nothing at all to do
with it.

Also. the using stuff is again, to deal with the connection resource issues.
In this case it is not really needed for adapter. However, by using the
using statement, IT will dispose stuff for you . So by explicitly calling
dispose on adapter and dataset, you are calling dispose twice which is
unecessary. I don't like the using on the dataadapter and dataset. I'd get
rid of them.

I would start from more typical code, watch the resources and then if you
feel you must do allof that
explicit stuff. And I feel the same as Marina about the GC. It is a very
complicated beast and there are rare occasions when you would override the
default GC behavior.

Lastly - when do you do something with the dataset? you fill it then dispose
it. Aren't you returning it from here?

trial 1


SqlDataAdapter TheAdapter = new SqlDataAdapter("select * from
customermaster",
DoodConnection)
DataSet TheCustomers = new DataSet()
TheCustomers.Tables.Add("CustomerMaster");
TheAdapter.Fill(TheCustomers, "CustomerMaster");

trial2 - just explicitly handling the connection

using (DoodConnection SqlConnection=new SqlConnection(myconnectionstring))
{
SqlDataAdapter TheAdapter = new SqlDataAdapter("select * from
customermaster", DoodConnection)
DataSet TheCustomers = new DataSet()
TheCustomers.Tables.Add("CustomerMaster");
TheAdapter.Fill(TheCustomers, "CustomerMaster");
return TheCustomers; //????
}
 
J

Julie Lerman

well, looks like Marina and I were typing reponses at the same time. :) We
are both telling you the same things.
 
G

Guest

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

Hotness, huh? ;-) I really didn't mean any disrespect to you or your
family. I hope you can tell them I am really not such a jerk after all!

Anyway, back to business...

I understand that if the connection was created separately that it would
have to be closed separately.

Now for the big answer, and you will probably shake your head in disgust,
but here goes: There are 78 columns in the customerMaster, most of which are
bit fields and small char/varchar fields (note there are no nchar/nvarchar
fields). It also contains a few smalldatetime and decimal(9,2) fields.
There are approximately 24,000 rows. Only managers would be interested in
this plethora of data, but I am in the designing/testing phases and I wanted
make sure the applications would handle the load under worst case scenario.
I could probably pare out some of the columns, or create different reports
for different types of customer data, but you know how managers are. In
addition, I have tried smaller sets of data (579 rows)and I still see
symptons of the problem. It seems to me that I could load this dataset, then
dispose it, and have the memory available for the next run of the report. By
available I mean available to the application, not necessarily the operating
system. So maybe I could expect to see the worker process jump up to a
couple hundred MB and stay there for a while, during which time the memory
would be reserved for subsequent requests. But the memory never gets
released, and subsequent requests consume more and more memory. Are these
assumptions false? If so, why?
 
G

Guest

It's very unkosher.
Yes, I know. I have stated that it is just test code - rudimentary at best.
I have added the "using" and GC based on suggestions found in other groups.
Again, the GC DID help a bit.
First of all, you do not have to call dispose on the adapter or the dataset. They
are managed objects.
Click to expand...
I always call dispose just as a matter of habit. I think that was based on
best practices from the early .net days.
Lastly - when do you do something with the dataset? you fill it then dispose it.
Aren't you returning it from here?
Click to expand...
Yes, as I said before, this is test code, the dataset is actually used as
the source for a report. The report code has been cut out of this segment.
I have tested this segment as it appears here and I get the same results, so
the memory issue appears to have nothing to do with the report and how it is
being managed.
 
C

Cor Ligthert [MVP]

Chad,

All the time do I have the idea that you are fetching to much data. No human
can handle that. A dataset is a typical class, to handle using an UI the
data.

For a more serialized operation you can probably better use a DataReader and
write the updates using the ExecuteNonQuery.

Now back to your dataset and my first thought I had al the time. Why is
there no "Where" clause in your SQL command.

I have not the idea that this amount of Data is for a disconnected medium as
a handheld or something like that.

Using this amount of data will not alone bring you in performance problems.
How you had the idea to handle updates from other users?

This was my first thought reading the messages, now you have even committed
that.

Cor
 
G

Guest

All the time do I have the idea that you are fetching to much data. No human
can handle that.
Click to expand...
If I was a betting man (and I am) I would bet you are right. But I am
setting this up for worse case scenario (WCS).
For a more serialized operation you can probably better use a DataReader and
write the updates using the ExecuteNonQuery.
Click to expand...
True, that would help, but I am thinking Crystal Reports will only take a
dataset as its reoprt source. I am going to load this up and try anyway.
Now back to your dataset and my first thought I had al the time. Why is
there no "Where" clause in your SQL command. WCS

Using this amount of data will not alone bring you in performance problems.
How you had the idea to handle updates from other users?
Click to expand...
This system only receives updates once a day.

Thanks, Cor.
 
M

Marina Levit [MVP]

In one of the posts, I sort of gave an overview of how the GC works. In any
case, calling Dispose on an object does not mean that its memory is
available to the application to use for other things. It's just not how it
works.

I am guessing even management does not want to see 24K worth of data all on
the same page in the report. Therefore, I recommend you change your
application to retrieve only the data required for the page currently being
viewed. Not only will the report run faster because you will need far less
data, but hopefully your memory issues will go away too.

For me, this is sort of as much as I know and out of ideas. Maybe someone
else on the newsgroup has some ideas. Otherwise you might need to open up a
support case with MS.
 
G

Guest

Cor,

NTILE. Very cool. I am sure both of the concepts you posted will come in
handy. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to you page and sort large dataset result in datagrid? 1

Top