ADO.NET dataset limit

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • RdS
    #1

    ADO.NET dataset limit

    hello,

    When I create a form that ties to dataset of table with only 20 records in
    it I understand that all 20 records are copied to my local computers memory
    for manipulation. Then if rows were modified the tableadapter class updates
    table in DB. However, I am curious to know what occurs if a table has
    thousands or 100's of thousands of rows in it. Does ado.net copy all of the
    rows to my local computers memory if I don't specify a condition for what
    records I want returned?

    For instance if my form will allow user to browse all customer records.
    Won't we start to have memory issues and slow initial performance while all
    records are copied to local storage? How does ADO handle large datasets?

    How should I handle such a case?

    Thanks in advance.


    Tags: None
    • Kevin Yu [MSFT]
      #2
      RE: ADO.NET dataset limit

      Hi RdS,

      Yes, as you understand, when you use a DataAdapter to fill the DataSet, it
      puts all the records that SELECT statement returns into the DataSet. If the
      SELECT statement returns 100's thousand rows, they will all be filled to
      the DataSet. Whether this operation will succeed depends on how big your
      memory is.

      However, in my opinion, if you have a 100's thousand database table, it is
      not a good idea to fill everything into the DataSet. Here are the reasons:

      1. As you know, memory will be used up, and apps are slowed down.
      2. A user cannot see every record since he's not a super man. :-)

      In this case, if a user need certain record(s) from the database, use WHERE
      clause in SELECT statement. It will return only the rows needed which saves
      memory.

      In certain cases, a user need to browse all the records, you can use paging
      to decrease the amount of data each time that comes to local memory.

      Kevin Yu
      Microsoft Online Community Support

      =============== =============== =============== =====
      Get notification to my posts through email? Please refer to
      http://msdn.microsoft.com/subscripti...ult.aspx#notif
      ications.
      Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
      where an initial response from the community or a Microsoft Support
      Engineer within 1 business day is acceptable. Please note that each follow
      up response may take approximately 2 business days as the support
      professional working with you may need further investigation to reach the
      most efficient resolution. The offering is not appropriate for situations
      that require urgent, real-time or phone-based interactions or complex
      project analysis and dump analysis issues. Issues of this nature are best
      handled working with a dedicated Microsoft Support Engineer by contacting
      Microsoft Customer Support Services (CSS) at
      http://msdn.microsoft.com/subscripti...t/default.aspx.
      =============== =============== =============== =====

      (This posting is provided "AS IS", with no warranties, and confers no
      rights.)

        Comment

        • RdS
          #3
          Re: ADO.NET dataset limit

          I understood why you wouldn't do and understand how to write SQL statements.
          I was just curious if VB would allow such a thing or if it cared. Also, if
          someone attempted to fill a dataset with 100's of thousands of records and
          the memory couldn't hold it what errors would be encountered?

          Thanks,

          "Kevin Yu [MSFT]" <v-kevy@online.mic rosoft.comwrote in message
          news:12Q8cxgsGH A.4400@TK2MSFTN GXA01.phx.gbl.. .
          Hi RdS,
          >
          Yes, as you understand, when you use a DataAdapter to fill the DataSet, it
          puts all the records that SELECT statement returns into the DataSet. If
          the
          SELECT statement returns 100's thousand rows, they will all be filled to
          the DataSet. Whether this operation will succeed depends on how big your
          memory is.
          >
          However, in my opinion, if you have a 100's thousand database table, it is
          not a good idea to fill everything into the DataSet. Here are the reasons:
          >
          1. As you know, memory will be used up, and apps are slowed down.
          2. A user cannot see every record since he's not a super man. :-)
          >
          In this case, if a user need certain record(s) from the database, use
          WHERE
          clause in SELECT statement. It will return only the rows needed which
          saves
          memory.
          >
          In certain cases, a user need to browse all the records, you can use
          paging
          to decrease the amount of data each time that comes to local memory.
          >
          Kevin Yu
          Microsoft Online Community Support
          >
          =============== =============== =============== =====
          Get notification to my posts through email? Please refer to
          http://msdn.microsoft.com/subscripti...ult.aspx#notif
          ications.
          Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
          where an initial response from the community or a Microsoft Support
          Engineer within 1 business day is acceptable. Please note that each follow
          up response may take approximately 2 business days as the support
          professional working with you may need further investigation to reach the
          most efficient resolution. The offering is not appropriate for situations
          that require urgent, real-time or phone-based interactions or complex
          project analysis and dump analysis issues. Issues of this nature are best
          handled working with a dedicated Microsoft Support Engineer by contacting
          Microsoft Customer Support Services (CSS) at
          http://msdn.microsoft.com/subscripti...t/default.aspx.
          =============== =============== =============== =====
          >
          (This posting is provided "AS IS", with no warranties, and confers no
          rights.)
          >

            Comment

            • Michel Posseth [MCP]
              #4
              Re: ADO.NET dataset limit

              You wil receive an out of memory exception

              regards

              Michel Posseth



              "RdS" <rds@nospam.nos pamschreef in bericht
              news:OGPeddksGH A.4380@TK2MSFTN GP05.phx.gbl...
              >I understood why you wouldn't do and understand how to write SQL
              >statements. I was just curious if VB would allow such a thing or if it
              >cared. Also, if someone attempted to fill a dataset with 100's of
              >thousands of records and the memory couldn't hold it what errors would be
              >encountered?
              >
              Thanks,
              >
              "Kevin Yu [MSFT]" <v-kevy@online.mic rosoft.comwrote in message
              news:12Q8cxgsGH A.4400@TK2MSFTN GXA01.phx.gbl.. .
              >Hi RdS,
              >>
              >Yes, as you understand, when you use a DataAdapter to fill the DataSet,
              >it
              >puts all the records that SELECT statement returns into the DataSet. If
              >the
              >SELECT statement returns 100's thousand rows, they will all be filled to
              >the DataSet. Whether this operation will succeed depends on how big your
              >memory is.
              >>
              >However, in my opinion, if you have a 100's thousand database table, it
              >is
              >not a good idea to fill everything into the DataSet. Here are the
              >reasons:
              >>
              >1. As you know, memory will be used up, and apps are slowed down.
              >2. A user cannot see every record since he's not a super man. :-)
              >>
              >In this case, if a user need certain record(s) from the database, use
              >WHERE
              >clause in SELECT statement. It will return only the rows needed which
              >saves
              >memory.
              >>
              >In certain cases, a user need to browse all the records, you can use
              >paging
              >to decrease the amount of data each time that comes to local memory.
              >>
              >Kevin Yu
              >Microsoft Online Community Support
              >>
              >============== =============== =============== ======
              >Get notification to my posts through email? Please refer to
              >http://msdn.microsoft.com/subscripti...ult.aspx#notif
              >ications.
              >Note: The MSDN Managed Newsgroup support offering is for non-urgent
              >issues
              >where an initial response from the community or a Microsoft Support
              >Engineer within 1 business day is acceptable. Please note that each
              >follow
              >up response may take approximately 2 business days as the support
              >professional working with you may need further investigation to reach the
              >most efficient resolution. The offering is not appropriate for situations
              >that require urgent, real-time or phone-based interactions or complex
              >project analysis and dump analysis issues. Issues of this nature are best
              >handled working with a dedicated Microsoft Support Engineer by contacting
              >Microsoft Customer Support Services (CSS) at
              >http://msdn.microsoft.com/subscripti...t/default.aspx.
              >============== =============== =============== ======
              >>
              >(This posting is provided "AS IS", with no warranties, and confers no
              >rights.)
              >>
              >
              >

                Comment

                • Kevin Yu [MSFT]
                  #5
                  Re: ADO.NET dataset limit

                  Yes, OutOfMemoryExce ption. You can check the following link for more
                  information

                  http://msdn.microsoft.com/library/de...us/cpref/html/
                  frlrfsystemouto fmemoryexceptio nclasstopic.asp

                  Kevin Yu
                  Microsoft Online Community Support

                  =============== =============== =============== =====

                  (This posting is provided "AS IS", with no warranties, and confers no
                  rights.)

                    Comment

                    Previous template Next
                    Working...