Performance and Memory Usage Consideration about DataTable and Dat

Discussion in 'Microsoft ADO .NET' started by Guest, Mar 23, 2007.

  1. Guest

    Guest Guest

    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!
     
    Guest, Mar 23, 2007
    #1
    1. Advertisements

  2. Guest

    RobinS Guest

    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" <> wrote in message
    news:...
    > 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!
     
    RobinS, Mar 23, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Re: Performance and Memory Usage Consideration about DataTable and

    ?? 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.
     
    Guest, Mar 23, 2007
    #3
  4. 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" <> wrote in message
    news:...
    > 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!
     
    Miha Markic [MVP C#], Mar 23, 2007
    #4
  5. 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
    > 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)

    --
    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/
     
    Miha Markic [MVP C#], Mar 23, 2007
    #5
  6. Guest

    Guest Guest

    Re: Performance and Memory Usage Consideration about DataTable and

    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?
     
    Guest, Mar 23, 2007
    #6
  7. Re: Performance and Memory Usage Consideration about DataTable and

    "Samuel" <> wrote in message
    news:...
    > Thanks Miha,
    >
    > I am the paranoid type :)


    A healthy dose of paranoia is good :)

    >
    > 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?


    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/
     
    Miha Markic [MVP C#], Mar 23, 2007
    #7
  8. 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" <> wrote in message
    news:...
    >
    > 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" <> wrote in message
    > news:...
    >> 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!

    >
    >
     
    William \(Bill\) Vaughn, Mar 23, 2007
    #8
  9. Guest

    RobinS Guest

    Re: Performance and Memory Usage Consideration about DataTable and

    "Samuel" <> wrote in message
    news:...
    > ?? Why can't you databind a datareader ??
    >
    > And datatable is not just an array of datarows.


    No, it's not *just* an array of data rows. But it can be treated as an
    array of data rows.

    > 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.
    >


    I didn't realize you were doing a web application, and stand corrected per
    the other posts. Good luck with your application.

    Robin S.
     
    RobinS, Mar 23, 2007
    #9
  10. Guest

    RobinS Guest

    "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
    news:...
    > 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.

    >
    > 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. ;-)

    Robin S.
     
    RobinS, Mar 23, 2007
    #10
  11. Guest

    RobinS Guest

    "William (Bill) Vaughn" <> wrote in message
    news:%...
    > 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.
     
    RobinS, Mar 23, 2007
    #11
  12. "RobinS" <> wrote in message
    news:...
    >
    > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
    > news:...
    >> 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/
     
    Miha Markic [MVP C#], Mar 23, 2007
    #12
  13. Guest

    RobinS Guest

    "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
    news:uVOA3$...
    >
    >
    > 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.
     
    RobinS, Mar 25, 2007
    #13
  14. Guest

    WenYuan Wang Guest

    Re: Performance and Memory Usage Consideration about DataTable and

    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
     
    WenYuan Wang, Mar 26, 2007
    #14
  15. Guest

    Guest Guest

    Re: Performance and Memory Usage Consideration about DataTable and

    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" <> wrote in message
    > news:...
    > > 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!

    >
    >
    >
     
    Guest, Mar 28, 2007
    #15
  16. Re: Re: Performance and Memory Usage Consideration about DataTable andDat

    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" <> wrote in message
    >> news:...



    >>> 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" <> wrote in message
    >>>> news:...



    >>>>> 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" <> wrote in message
    >>>>>>> news:...
    >>>>>>>
    >>>>>>> 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" <> wrote in message
    >>>>>>>> news:...



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


    >>>>>>>>> "Samuel" <> wrote in message
    >>>>>>>>> news:...
    >>>>>>>>>
    >>>>>>>>> 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:...
    >>>>>>>>>>
    >>>>>>>>>> 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" <> wrote in message
    >>>>>>>>>>>> news:...
    >>>>>>>>>>>>
    >>>>>>>>>>>> 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/tutorial...5064/sharepoint-2010-using-external-list.aspx
     
    Damien Osborn, Mar 11, 2011
    #16
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neil Price
    Replies:
    1
    Views:
    640
  2. Guest

    Consideration of Master-Detail Update

    Guest, May 6, 2004, in forum: Microsoft ADO .NET
    Replies:
    0
    Views:
    193
    Guest
    May 6, 2004
  3. Ashish Sheth

    DataTable.NewRow and DataTable.LoadDataRow

    Ashish Sheth, Jul 7, 2004, in forum: Microsoft ADO .NET
    Replies:
    3
    Views:
    5,809
    Samuel L Matzen
    Jul 7, 2004
  4. C.Anand via DotNetMonster.com

    Retrieving datarows using DataTable.Select method and adding it to new DataTable

    C.Anand via DotNetMonster.com, Apr 4, 2005, in forum: Microsoft ADO .NET
    Replies:
    4
    Views:
    3,216
    Cor Ligthert
    Apr 4, 2005
  5. Gordian
    Replies:
    2
    Views:
    2,948
    Sahil Malik [MVP]
    Jul 28, 2005
Loading...

Share This Page