PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

Performance and Memory Usage Consideration about DataTable and Dat

 
 
RobinS
Guest
Posts: n/a
 
      23rd Mar 2007

"William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Ah, but in ASP net you can bind to a DataReader--just not in a Windows
> Forms application.


Thanks, Bill, I stand corrected. I didn't know you could do that, because I
don't do ASP (yet).

Robin S.


 
Reply With Quote
 
 
 
 
Miha Markic [MVP C#]
Guest
Posts: n/a
 
      24th Mar 2007

"RobinS" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:(E-Mail Removed)...
>> Hi Robin,
>>
>> Few minor corrections inline
>>
>>> Why are you so concerned about the memory taken up by the datatable?
>>> Once it goes out of scope, the GC will know it can be cleared. If it has
>>> a Dispose method, you can invoke it and the GC might dispose of it
>>> sooner.

>>
>> No, it won't. Dispose doesn't affect the garbage collection timing at
>> all - at least for the instance Dispose is called on.

>
> I didn't say that Disposing of the object would make the GC dispose of it
> right away. I said it might help it dispose of it sooner, because it will
> know it can be disposed of. So you chide me for saying that, and then you
> post this:
>
>>And letting GC to do its job is just fine, it will take care of it as soon
>>as the instance goes out of the scope (you might set the variable to null
>>asap to made it available to GC sooner).

>
> which seems terribly similar to what I said, except you tell him to set
> the variable to null instead of calling the Dispose() method. I don't even
> know if a datatable *has* a dispose method. The point was, make sure the
> GC knows it *can* be disposed of.


No, no, GC doesn't care about Dispose method at all. The only thing that
makes a garbage collection faster (and is relevant to garbage collection) is
to eliminate the outer references to the graph - in our case we eliminate
the only reference, that is setting variable to null.
BTW, Dispose is mostly used to release unmanaged resources.

>>
>> I
>>> can't believe an array of the same information would take up a lot less
>>> information. After all a data table is an array of DataRows.

>>
>> DataTable is a bit more than that. I guess if we want to find the
>> difference in size we should load same rows in table and into an array,
>> serialize both and compare its sizes (or compare sizes using a memory
>> profiler)

>
> Yes, that's an excellent idea. And I know the DataTable is more than that,
> but you have to admit, it also acts as an array of data rows, in addition
> to all the other stuff it does. ;-)


Sure, I didn't say that it isn't some sort of an array internally.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

 
Reply With Quote
 
 
 
 
RobinS
Guest
Posts: n/a
 
      25th Mar 2007

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:uVOA3$(E-Mail Removed)...
>
>
> No, no, GC doesn't care about Dispose method at all. The only thing that
> makes a garbage collection faster (and is relevant to garbage collection)
> is to eliminate the outer references to the graph - in our case we
> eliminate the only reference, that is setting variable to null.
> BTW, Dispose is mostly used to release unmanaged resources.


Thanks for clarifying that for me.


Robin S.


 
Reply With Quote
 
WenYuan Wang
Guest
Posts: n/a
 
      26th Mar 2007
Hi,

I agree with Miha. DataTable will provide more features for you than
ArrayList.

> I am unable to use this technique to get more than one ArrayList (for
> multiple resultset reader). Is it the limitation of the GetEnumerator
> method?


Did you receive an error message after executing reader.NextResult? I have
tested with your code just now, however it works fine for me so far. Would
you please past some exact error message? So that we can performance more
analyse.

Have a great day,
Sincerely,
Wen Yuan

 
Reply With Quote
 
=?Utf-8?B?TWlsb3N6IFNrYWxlY2tpIFtNQ0FEXQ==?=
Guest
Posts: n/a
 
      28th Mar 2007
Robin,

In addition to everyone's reply,
1. Yes, you can bind controls to datareader (ASP.NET only),
2. Yes you can read many results sets (see NextResult() method)
3. Have in mind DataReader is used internally in DbDataAdapter.Fill method
for populating the datatable(s) anyway.

Regards
--
Milosz


"RobinS" wrote:

>
> You can't data bind to a DataReader; it only reads one record at a time. It
> reads fast, but you have no retainability unless you store the rows
> somewhere.
>
> You can convert a datareader into a datatable, but why not just read it as
> a DataTable.
>
> Why are you so concerned about the memory taken up by the datatable? Once
> it goes out of scope, the GC will know it can be cleared. If it has a
> Dispose method, you can invoke it and the GC might dispose of it sooner. I
> can't believe an array of the same information would take up a lot less
> information. After all a data table is an array of DataRows.
>
> That's my two cents' worth.
> Robin S.
> -------------------------
> "Samuel" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> >
> > How do I free up the memory taken up by the .NET datatable and dataset
> > after
> > I finished working with them? How do I HELP the GC to do its job sooner
> > to
> > release memory taken up by DataTable/DataSet?
> >
> > I am worried about memory usage. Should I be using DataReader
> > exclusively?
> > But complex databinding is going to cause problem to me because it is
> > going
> > to keep the connection open for longer than necessary.
> >
> > Would it help if I create an arraylist of DbDataRecord from DataReader
> > instead?
> >
> > If reader.HasRows Then
> > For Each rec As System.Data.Common.DbDataRecord In reader
> > arr.Add(rec)
> > Next
> > End If
> >
> > I am guessing that arraylist takes up less memory than a DataTable. Am I
> > correct? Or is there a way for me to check how much memory each takes up
> > (datatable v.s. Arraylist of DbDataRecord)?
> >
> > Then, how do I use this technique to construct more than one arraylist
> > when
> > the datareader returns more than one result set? I can only get the first
> > resultset and i don't know why. It seems that the reader is closed
> > automatically when I use this technique.
> >
> > Another question I have is about DataTable's DataTableReader. What's the
> > point to use a DataTableReader? Does it make databinding faster than
> > binding
> > to Datatable itself? I am sure this would not take less memory so why?
> >
> > Thanks for any advice you can provide!

>
>
>

 
Reply With Quote
 
Damien Osborn
Guest
Posts: n/a
 
      11th Mar 2011
Saw this thread and had to reply, I have been dealing with datatables myself lately and have had to optimize some poor code on a very high traffic website. The MSDN seems to disagree with you (see ref: http://msdn.microsoft.com/en-us/magazine/cc163854.aspx) about garbage collection.

You always close your connections and dispose of your objects as soon as you are done with them. You can get away with not doing this for certain apps or websites that are low volume but high capacity will always reveal the flaws in your code. What works for 500 requests a second will not translate to 6500 for example.

I just had a situation where I load tested a page where the developers used datatables all over the place and never called .dispose. At 900 req/sec to a single Server 2008 box many 500 server errors were generated. I added calls to .dispose for every datatable and reran the load test, no more errors. Why? because garbage collection is not to be relied on for high performance code, you can never be sure when your resources will be freed. It's best to dispose of your objects immediately after you're finished with them.

In my case not closing the datatables was using up too many connections in the connection pool and not freeing them for use fast enough. Now I could have raised the maximum # of connections on the pool (which is what was done originally), but as you scale out that becomes a problem because now these connections are being held open to the sql server(not closing fast enough) and with enough traffic will crush sql server, so raising the # of connections shifts the load to sqlserver which is not appropriate.

Using .dispose not only benefits SQL Server but it will help control the resources of the web server in a more efficient manner. Remember best practices people, quick and dirty gets the job done quickly but not correctly.

> On Thursday, March 22, 2007 7:24 PM programme wrote:


> Hi,
>
> How do I free up the memory taken up by the .NET datatable and dataset after
> I finished working with them? How do I HELP the GC to do its job sooner to
> release memory taken up by DataTable/DataSet?
>
> I am worried about memory usage. Should I be using DataReader exclusively?
> But complex databinding is going to cause problem to me because it is going
> to keep the connection open for longer than necessary.
>
> Would it help if I create an arraylist of DbDataRecord from DataReader
> instead?
>
> If reader.HasRows Then
> For Each rec As System.Data.Common.DbDataRecord In reader
> arr.Add(rec)
> Next
> End If
>
> I am guessing that arraylist takes up less memory than a DataTable. Am I
> correct? Or is there a way for me to check how much memory each takes up
> (datatable v.s. Arraylist of DbDataRecord)?
>
> Then, how do I use this technique to construct more than one arraylist when
> the datareader returns more than one result set? I can only get the first
> resultset and i don't know why. It seems that the reader is closed
> automatically when I use this technique.
>
> Another question I have is about DataTable's DataTableReader. What's the
> point to use a DataTableReader? Does it make databinding faster than binding
> to Datatable itself? I am sure this would not take less memory so why?
>
> Thanks for any advice you can provide!



>> On Friday, March 23, 2007 12:35 AM RobinS wrote:


>> You can't data bind to a DataReader; it only reads one record at a time. It
>> reads fast, but you have no retainability unless you store the rows
>> somewhere.
>>
>> You can convert a datareader into a datatable, but why not just read it as
>> a DataTable.
>>
>> Why are you so concerned about the memory taken up by the datatable? Once
>> it goes out of scope, the GC will know it can be cleared. If it has a
>> Dispose method, you can invoke it and the GC might dispose of it sooner. I
>> can't believe an array of the same information would take up a lot less
>> information. After all a data table is an array of DataRows.
>>
>> That's my two cents' worth.
>> Robin S.
>> -------------------------
>> "Samuel" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...



>>> On Friday, March 23, 2007 12:56 AM programme wrote:


>>> ?? Why can't you databind a datareader ??
>>>
>>> And datatable is not just an array of datarows.
>>>
>>> And DataTable's Dispose method does nothing at all because it is a managed
>>> object. The method is there simply because it inherits
>>> MarchalByValueComponent.
>>>
>>> I am concerned about memory usage because my application is currently being
>>> used on busy websites and I need to make sure everything works smoothly. On a
>>> typical day there are more than 10,000 records being inserted into a table
>>> and the data are constantly queried and databound and there are thousands of
>>> concurrent users. This is why I am much more concerned about memory usage
>>> than anything.



>>>> On Friday, March 23, 2007 4:29 AM Miha Markic [MVP C#] wrote:


>>>> Hi there,
>>>>
>>>> Your situation is mostly theoretical one as you shouldn't load tons of rows
>>>> anyway.
>>>> And letting GC to do its job is just fine, it will take care of it as soon
>>>> as the instance goes out of the scope (you might set the variable to null
>>>> asap to made it available to GC sooner).
>>>> The only thing you might do is to force garbage collection but for doing
>>>> that you should really have a good reason.
>>>>
>>>> --
>>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia]
>>>> RightHand .NET consulting & development www.rthand.com
>>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>>>> "Samuel" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...



>>>>> On Friday, March 23, 2007 4:33 AM Miha Markic [MVP C#] wrote:


>>>>> Hi Robin,
>>>>>
>>>>> Few minor corrections inline
>>>>>
>>>>>
>>>>> No, it won't. Dispose doesn't affect the garbage collection timing at all -
>>>>> at least for the instance Dispose is called on.
>>>>>
>>>>> I
>>>>>
>>>>> DataTable is a bit more than that. I guess if we want to find the difference
>>>>> in size we should load same rows in table and into an array, serialize both
>>>>> and compare its sizes (or compare sizes using a memory profiler)
>>>>>
>>>>> --
>>>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia]
>>>>> RightHand .NET consulting & development www.rthand.com
>>>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/



>>>>>> On Friday, March 23, 2007 6:25 AM programme wrote:


>>>>>> Thanks Miha,
>>>>>>
>>>>>> I am the paranoid type
>>>>>>
>>>>>> And you are correct: I am not loading a ton of rows and there is no serious
>>>>>> data manipulation on databinding, so DataTable should work just fine for me.
>>>>>>
>>>>>> I found that binding to ArrayList is faster than DataTable, but there is a
>>>>>> problem about multiple resultset reader as I can't get more than one
>>>>>> Arraylist using the following code:
>>>>>>
>>>>>> Do
>>>>>>
>>>>>> Dim arr As New ArrayList
>>>>>>
>>>>>> If reader.HasRows Then
>>>>>> For Each rec As System.Data.Common.DbDataRecord
>>>>>> In reader
>>>>>> arr.Add(rec)
>>>>>> Next
>>>>>> End If
>>>>>>
>>>>>> allHoldingData.Add(arr)
>>>>>>
>>>>>> Loop While reader.NextResult
>>>>>>
>>>>>> I am unable to use this technique to get more than one ArrayList (for
>>>>>> multiple resultset reader). Is it the limitation of the GetEnumerator method?



>>>>>>> On Friday, March 23, 2007 6:52 AM Miha Markic [MVP C#] wrote:


>>>>>>> "Samuel" <(E-Mail Removed)> wrote in message
>>>>>>> news:(E-Mail Removed)...
>>>>>>>
>>>>>>> A healthy dose of paranoia is good :-)
>>>>>>>
>>>>>>>
>>>>>>> Not sure. Is NextResult always returning false?
>>>>>>> And btw, I prefer DataTable over an array because the performance is
>>>>>>> marginally different (and most of the time you loose fetching data through
>>>>>>> the network) and it is less code.
>>>>>>>
>>>>>>> --
>>>>>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia]
>>>>>>> RightHand .NET consulting & development www.rthand.com
>>>>>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/



>>>>>>>> On Friday, March 23, 2007 11:06 AM William \(Bill\) Vaughn wrote:


>>>>>>>> Ah, but in ASP net you can bind to a DataReader--just not in a Windows Forms
>>>>>>>> application.
>>>>>>>> Before you go off writing a bunch of code to handle rowsets or multiple
>>>>>>>> resultsets containing rowsets and populating arrays, consider that MS has
>>>>>>>> spent considerable time on performance in this area. ADO.NET can populate
>>>>>>>> multiple arrays for you in a single line of code: Use the DataAdapter Fill
>>>>>>>> method. It deals with a litany of issues as well--including connection
>>>>>>>> management (the bane of many an ASP app). You say a DataTable is not an
>>>>>>>> array? Ah, behind the scenes it's not really much more than an untyped
>>>>>>>> array. You can also load a DataTable with one line of code: the new Load
>>>>>>>> method works better than any manual DataTable populator I've seen.
>>>>>>>>
>>>>>>>> Typically, more performance can be gained in the questions you ask (your
>>>>>>>> queries) than all of the fancy footwork dealing with the answer (the
>>>>>>>> rowsets) that's returned.
>>>>>>>>
>>>>>>>> hth
>>>>>>>>
>>>>>>>> --
>>>>>>>> ____________________________________
>>>>>>>> William (Bill) Vaughn
>>>>>>>> Author, Mentor, Consultant
>>>>>>>> Microsoft MVP
>>>>>>>> INETA Speaker
>>>>>>>> www.betav.com/blog/billva
>>>>>>>> www.betav.com
>>>>>>>> Please reply only to the newsgroup so that others can benefit.
>>>>>>>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>>>>>>> __________________________________
>>>>>>>> Visit www.hitchhikerguides.net to get more information on my latest book:
>>>>>>>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>>>>>>>> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
>>>>>>>> -----------------------------------------------------------------------------------------------------------------------
>>>>>>>>
>>>>>>>> "RobinS" <(E-Mail Removed)> wrote in message
>>>>>>>> news:(E-Mail Removed)...



>>>>>>>>> On Friday, March 23, 2007 4:23 PM RobinS wrote:


>>>>>>>>> "Samuel" <(E-Mail Removed)> wrote in message
>>>>>>>>> news:(E-Mail Removed)...
>>>>>>>>>
>>>>>>>>> No, it's not *just* an array of data rows. But it can be treated as an
>>>>>>>>> array of data rows.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I didn't realize you were doing a web application, and stand corrected per
>>>>>>>>> the other posts. Good luck with your application.
>>>>>>>>>
>>>>>>>>> Robin S.



>>>>>>>>>> On Friday, March 23, 2007 4:28 PM RobinS wrote:


>>>>>>>>>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
>>>>>>>>>> news:(E-Mail Removed)...
>>>>>>>>>>
>>>>>>>>>> I didn't say that Disposing of the object would make the GC dispose of it
>>>>>>>>>> right away. I said it might help it dispose of it sooner, because it will
>>>>>>>>>> know it can be disposed of. So you chide me for saying that, and then you
>>>>>>>>>> post this:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> which seems terribly similar to what I said, except you tell him to set the
>>>>>>>>>> variable to null instead of calling the Dispose() method. I don't even know
>>>>>>>>>> if a datatable *has* a dispose method. The point was, make sure the GC
>>>>>>>>>> knows it *can* be disposed of.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Yes, that's an excellent idea. And I know the DataTable is more than that,
>>>>>>>>>> but you have to admit, it also acts as an array of data rows, in addition
>>>>>>>>>> to all the other stuff it does. ;-)
>>>>>>>>>>
>>>>>>>>>> Robin S.



>>>>>>>>>>> On Friday, March 23, 2007 4:28 PM RobinS wrote:


>>>>>>>>>>> Thanks, Bill, I stand corrected. I did not know you could do that, because I
>>>>>>>>>>> do not do ASP (yet).
>>>>>>>>>>>
>>>>>>>>>>> Robin S.



>>>>>>>>>>>> On Friday, March 23, 2007 6:05 PM Miha Markic [MVP C#] wrote:


>>>>>>>>>>>> "RobinS" <(E-Mail Removed)> wrote in message
>>>>>>>>>>>> news:(E-Mail Removed)...
>>>>>>>>>>>>
>>>>>>>>>>>> No, no, GC doesn't care about Dispose method at all. The only thing that
>>>>>>>>>>>> makes a garbage collection faster (and is relevant to garbage collection) is
>>>>>>>>>>>> to eliminate the outer references to the graph - in our case we eliminate
>>>>>>>>>>>> the only reference, that is setting variable to null.
>>>>>>>>>>>> BTW, Dispose is mostly used to release unmanaged resources.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> Sure, I didn't say that it isn't some sort of an array internally.
>>>>>>>>>>>>
>>>>>>>>>>>> --
>>>>>>>>>>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia]
>>>>>>>>>>>> RightHand .NET consulting & development www.rthand.com
>>>>>>>>>>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/



>>>>>>>>>>>>> On Saturday, March 24, 2007 10:54 PM RobinS wrote:


>>>>>>>>>>>>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
>>>>>>>>>>>>>
>>>>>>>>>>>>> Thanks for clarifying that for me.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Robin S.



>>>>>>>>>>>>>> On Monday, March 26, 2007 7:01 AM v-wywan wrote:


>>>>>>>>>>>>>> Hi,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I agree with Miha. DataTable will provide more features for you than
>>>>>>>>>>>>>> ArrayList.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Did you receive an error message after executing reader.NextResult? I have
>>>>>>>>>>>>>> tested with your code just now, however it works fine for me so far. Would
>>>>>>>>>>>>>> you please past some exact error message? So that we can performance more
>>>>>>>>>>>>>> analyse.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Have a great day,
>>>>>>>>>>>>>> Sincerely,
>>>>>>>>>>>>>> Wen Yuan



>>>>>>>>>>>>>>> On Tuesday, March 27, 2007 7:02 PM mily24 wrote:


>>>>>>>>>>>>>>> Robin,
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> In addition to everyone's reply,
>>>>>>>>>>>>>>> 1. Yes, you can bind controls to datareader (ASP.NET only),
>>>>>>>>>>>>>>> 2. Yes you can read many results sets (see NextResult() method)
>>>>>>>>>>>>>>> 3. Have in mind DataReader is used internally in DbDataAdapter.Fill method
>>>>>>>>>>>>>>> for populating the datatable(s) anyway.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Regards
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>> Milosz
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> "RobinS" wrote:



>>>>>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>>>>>> SharePoint 2010 Using External List
>>>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials...rnal-list.aspx

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract data from a Datatable into another DataTable or dat PIPUSER Microsoft Dot NET 1 18th Mar 2009 09:36 PM
My available memory is down to very little, yet my PF Usage is 8 gigabytes (about how much memory is on the box), sql server keeps having memory issues yet the sqlservr.exe is using hardly any memory.. how to fix this? is there some way to limit how Daniel Microsoft Windows 2000 Security 1 30th Aug 2007 07:38 AM
My available memory is down to very little, yet my PF Usage is 8 gigabytes (about how much memory is on the box), sql server keeps having memory issues yet the sqlservr.exe is using hardly any memory.. how to fix this? is there some way to limit how Daniel Microsoft Windows 2000 1 30th Aug 2007 01:22 AM
My available memory is down to very little, yet my PF Usage is 8 gigabytes (about how much memory is on the box), sql server keeps having memory issues yet the sqlservr.exe is using hardly any memory.. how to fix this? is there some way to limit how Daniel Microsoft Windows 2000 Networking 0 28th Aug 2007 12:38 AM
My available memory is down to very little, yet my PF Usage is 8 gigabytes (about how much memory is on the box), sql server keeps having memory issues yet the sqlservr.exe is using hardly any memory.. how to fix this? is there some way to limit how Daniel Microsoft Windows 2000 Advanced Server 0 28th Aug 2007 12:38 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:53 PM.