locked
How much data datareader can fetch at a time RRS feed

  • Question

  • suppose some one using data reader to fetch data from db and when the data reader execute sql then there is suppose 1000 data in table so tell me data reader will fetch all data or fetch some of the data and store in network buffer. when all data will be taken from network buffer then it will fetch again some data.

    so i like to know how data reader calculate how much data it need to fetch in one round trip from db?

    please guide. thanks

    Thursday, May 19, 2016 9:14 AM

Answers

  • DataReader works mostly (but not exactly) like a Enumerator. The main difference is that DR will not break if the underlying data is changed (it has no way to check that).
    The exact tresholds can depend on the Framework version, Framework implementation and even RAM it can work with. We know how it will behave, but not exactly how that behavior comes to be.

    I can only give you generalsied advice for Optimising DB access (DR's design basically follows this idea):

    - Try to retrieve as little data as possible in one go. User can not reliably work with more then 20-100 rows (depending on number of columns).
    Do not do filtering, paging or the like on UI level. Do it on query level, always.

    - At the same time, try to retrieve as much and as highly processed data in a single query as possible. Don't store numeric types, Dates, IP adresses or the like in string/nvarchar form.
    Don't poll a bunch of records piecemeal. Poll them in small bursts (if you do not already do them all via paging).

    - Finally if possible keep all processing and data integrity confinded to the DBMS. Retrieve, process, write causes a ton of useless network traffic (both ways) and could run into a update race condition case.

    • Proposed as answer by Albert_Zhang Saturday, May 28, 2016 5:15 AM
    • Marked as answer by DotNet Wang Monday, May 30, 2016 10:53 AM
    Thursday, May 19, 2016 10:39 AM
  • Short answer - it fetches it in blocks

    It is up to the provider to return as much data as it wants. It is the most efficient use of memory however as it won't run out of memory irrelevant of how much data you are returning (although your app may if it keeps the data in memory).  You can envision it returning a single row at a time. Realistically this is inefficient so it will actually read a block of data at a time. But the block size is implementation defined and can vary. Logically each call returns the next row of data and the buffering is occurring under the hood just like file operations work. In theory if you never read all the rows then not all the data has to be sent over the network.

    I tend to recommend that you always use a data reader to read data from the DB except in the case you actually want to get a dataset. Datasets require the entire results be stored in memory so they are inefficient and heavy. Under the hood a reader is still being used to read the data but it is simply populating the dataset. If you intend to use your own custom types to represent the data then there is no reason not to use a reader.

    Michael Taylor
    http://www.michaeltaylorp3.net

    • Proposed as answer by Albert_Zhang Saturday, May 28, 2016 5:14 AM
    • Marked as answer by DotNet Wang Monday, May 30, 2016 10:53 AM
    Friday, May 20, 2016 2:38 PM

All replies

  • DataReader works mostly (but not exactly) like a Enumerator. The main difference is that DR will not break if the underlying data is changed (it has no way to check that).
    The exact tresholds can depend on the Framework version, Framework implementation and even RAM it can work with. We know how it will behave, but not exactly how that behavior comes to be.

    I can only give you generalsied advice for Optimising DB access (DR's design basically follows this idea):

    - Try to retrieve as little data as possible in one go. User can not reliably work with more then 20-100 rows (depending on number of columns).
    Do not do filtering, paging or the like on UI level. Do it on query level, always.

    - At the same time, try to retrieve as much and as highly processed data in a single query as possible. Don't store numeric types, Dates, IP adresses or the like in string/nvarchar form.
    Don't poll a bunch of records piecemeal. Poll them in small bursts (if you do not already do them all via paging).

    - Finally if possible keep all processing and data integrity confinded to the DBMS. Retrieve, process, write causes a ton of useless network traffic (both ways) and could run into a update race condition case.

    • Proposed as answer by Albert_Zhang Saturday, May 28, 2016 5:15 AM
    • Marked as answer by DotNet Wang Monday, May 30, 2016 10:53 AM
    Thursday, May 19, 2016 10:39 AM
  • Short answer - it fetches it in blocks

    It is up to the provider to return as much data as it wants. It is the most efficient use of memory however as it won't run out of memory irrelevant of how much data you are returning (although your app may if it keeps the data in memory).  You can envision it returning a single row at a time. Realistically this is inefficient so it will actually read a block of data at a time. But the block size is implementation defined and can vary. Logically each call returns the next row of data and the buffering is occurring under the hood just like file operations work. In theory if you never read all the rows then not all the data has to be sent over the network.

    I tend to recommend that you always use a data reader to read data from the DB except in the case you actually want to get a dataset. Datasets require the entire results be stored in memory so they are inefficient and heavy. Under the hood a reader is still being used to read the data but it is simply populating the dataset. If you intend to use your own custom types to represent the data then there is no reason not to use a reader.

    Michael Taylor
    http://www.michaeltaylorp3.net

    • Proposed as answer by Albert_Zhang Saturday, May 28, 2016 5:14 AM
    • Marked as answer by DotNet Wang Monday, May 30, 2016 10:53 AM
    Friday, May 20, 2016 2:38 PM