none
DataTable object maximum size RRS feed

  • Question

  • hi, 

    I am referring to https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?view=netcore-3.1  (or its .net Framework equivalent)


    There is no official confirmation about the maximum size of data that can be held in a DataTable object (sorry, if it is there in the documentation and am missing it)


    In general, 1 row can be of any number of columns. And, each of these columns can be of any data type, resulting in varying size of storage.  More the columns, higher the MB to hold those many rows in DataTable object.  Is the amount of data that can be put in a DataTable object,  DotNet version dependent or underlying machine configuration dependent?  In either case, what is this limit (rows wise and/or MB wise)?


    thank you


    Saturday, July 11, 2020 5:23 PM

All replies

  • The maximum size is going to be the available size of what can fit in memory. This can vary wildly by application. If your app is an x86 app then you are limited to 4GB total but 2GB of app space (3GB if using the appropriate compile flag). That means the max amount of data you can store is 2GB - executable code - stack space.

    Datatable uses arrays under the hood to store the rows and each row uses an array to store the fields. All arrays in .NET (like pretty much every other language) are allocated contiguously in memory so the largest array that can be allocated by a process is the largest contiguous block of memory available in the process. Note that this part is very important. It is quite possible to get an out of memory error even when your process has plenty of memory left. This will happen with arrays if you attempt to allocate an array that cannot be fit into the available contiguous memory left in the process. In general the larger the array size the more likely it'll happen.

    Ignoring any optimizations the library may use to reduce # amount of allocations it takes and just focusing on the arrays that make up the Datatable itself you can determine approximately how many arrays you're dealing with. Since each array must be contiguous you can run out of space attempting to allocate any of them. Doing some dirty math you have a 1 array with an element for each column. The rows are stored in a separate array. Each DataRow consists of an array of values (one for each column). Furthermore DataRow tracks the original and modified values so you actually have duplicate data. This is just for the arrays. As for the elements you are storing a DataColumn in the column arrays and a primitive or object in each row's arrays so that adds to the total required memory.

    Finally, at least for Datatable the array length is capped at a size of an int so you're looking at 2 billion elements. .NET itself allows larger arrays now but I don't believe DataTable supports it. So irrelevant of memory you are limited to 2 billion per array but you are probably going to run out of space long before then, at least on x86.

    Does this sound hard? Is it not really answering your question? That's because we cannot really answer it. There is no way to know how large a Datatable can fit into memory without actually trying to do it. There are simply too many variables. If you want just maximals then 2 billion (length of an array). But you won't get anywhere near that. If you're dealing with moderate size tables with a handful of columns then you can fit more rows.

    However if you're really looking at a Datatable and concerned about space then you aren't going to use a Datatable. Datatables are not designed to be optimized in memory. They have way too much overhead. In general if you're looking to load more than a couple of hundred rows you should be using a data reader in combination with custom objects. They are far more memory efficient. If you must use a Datatable then you're going to need to page in data using a query built for that. At that point you'll use a page size that is reasonable for your app.


    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, July 11, 2020 9:02 PM
    Moderator
  • hi

    No idea about number of columns But there is rows limits

    1= The maximum number of rows that a DataTable can store is 16,777,216
    https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?view=netcore-3.1

    Thanks and regards

    Sunday, July 12, 2020 11:23 AM
  • Hi,

    Has your issue been resolved?

    If so, please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 21, 2020 8:10 AM