The DataTable / DataSet size

Hi,

We do ADO.NEt programming only on the time-to time basis, so we don't have
the real deep experience in this matter.

Every where in MSDN or other documentation I can read that DataTable/DataSet
is designated to keeping disconnected data from a database. So far, so good.
But I have never seen any recommendation about how (or when) to control the
size of these objects.

For example letâ??s say we have a table that can be small at the beginning and
we keep this small amount of records in a disconnected DataTable object.
After several months the size of the table in a SQL database can grow to
several-docents of thousand records. How should the client side application
respond to this change, or, when we should start to worry about the size of
fetched data? I know it depends from available memory and so onâ?¦. But anyway,
what is your typical size of DataTable object (MBs, number of recordsâ?¦).



Thanks,
Lubomir

Sheng
Mon Oct 15 15:12:27 PDT 2007

If the datatable is too large to be transferred, use paging and/or filtering

--
Sheng Jiang
Microsoft MVP in VC++
"Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
news:15B1D1A2-D1C7-4626-9338-247529968CAC@microsoft.com...
> Hi,
>
> We do ADO.NEt programming only on the time-to time basis, so we don't have
> the real deep experience in this matter.
>
> Every where in MSDN or other documentation I can read that
DataTable/DataSet
> is designated to keeping disconnected data from a database. So far, so
good.
> But I have never seen any recommendation about how (or when) to control
the
> size of these objects.
>
> For example let's say we have a table that can be small at the beginning
and
> we keep this small amount of records in a disconnected DataTable object.
> After several months the size of the table in a SQL database can grow to
> several-docents of thousand records. How should the client side
application
> respond to this change, or, when we should start to worry about the size
of
> fetched data? I know it depends from available memory and so on.. But
anyway,
> what is your typical size of DataTable object (MBs, number of records.).
>
>
>
> Thanks,
> Lubomir
>

Cor
Mon Oct 15 20:38:01 PDT 2007

Lubomir,

It looks as if the DataSet is an ofline database but it is not, the meaning
is to take only the parts of the database that are needed (which can in some
cases be the whole database).

This you can done by using by instance the "where" statement in your SQL
script.

Cor

Miha
Mon Oct 15 23:39:06 PDT 2007

As Cor said, you should load only data you need for a certain process,
process it and store it back. There is no need to keep enormous amounts of
data in DataSet.
--
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/

Lubomir
Thu Oct 18 10:36:01 PDT 2007

Hi,
Thanks for answers.

Yes, we are using filters to fetch the data from database. However we are
concerned that when database will be loaded with real data, the amount of
fetched records will be too big down the road.

Maybe we are too much worried on the other hand. What I was looking for is
to get the picture about the size of the DataTable used by other, more
experienced database developers.

In our case I think the disconnected datatable will not have more than 100
000 records with 8-10 columns - in teh worst scenario. Most probably it will
be much less.

Regards,
Lubomir



"Miha Markic" wrote:

> As Cor said, you should load only data you need for a certain process,
> process it and store it back. There is no need to keep enormous amounts of
> data in DataSet.
> --
> 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
Thu Oct 18 12:04:27 PDT 2007

One way to figure approximate size is by *binary* serializing to a
memorystream. Once you are done you can check the size of the stream.

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

"Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
news:BC7B74E6-687A-4D06-B09B-43E2FEC4590D@microsoft.com...
> Hi,
> Thanks for answers.
>
> Yes, we are using filters to fetch the data from database. However we are
> concerned that when database will be loaded with real data, the amount of
> fetched records will be too big down the road.
>
> Maybe we are too much worried on the other hand. What I was looking for is
> to get the picture about the size of the DataTable used by other, more
> experienced database developers.
>
> In our case I think the disconnected datatable will not have more than 100
> 000 records with 8-10 columns - in teh worst scenario. Most probably it
> will
> be much less.
>
> Regards,
> Lubomir
>
>
>
> "Miha Markic" wrote:
>
>> As Cor said, you should load only data you need for a certain process,
>> process it and store it back. There is no need to keep enormous amounts
>> of
>> data in DataSet.
>> --
>> 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
Thu Oct 18 12:05:25 PDT 2007

Almost forgot. It doesn't matter the size of the DataTable if you have
enough RAM.
100,000 records seems like a lot to me. I would prefer working on smaler
sets.
--
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/

Jim
Thu Oct 18 14:31:35 PDT 2007

We had to import some data into SQL Server, but we ran into data exception
problems with "wierd" data. So, we needed a simple way to view the data.
Since there were over 256 columns, we could not use Microsoft Access for
this.

What to do? What to do?

I know. We will load it into a dataset and bind a grid to it to see what we
were dealing with.

Worked great.

By the way, the number of rows exceeded 2,000,000. Not bad for a six year
old cheap desktop with 256 megs of memory and .NET 1.1. Microsoft obviously
did something very clever under the hood.


"Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
news:BC7B74E6-687A-4D06-B09B-43E2FEC4590D@microsoft.com...
> Hi,
> Thanks for answers.
>
> Yes, we are using filters to fetch the data from database. However we are
> concerned that when database will be loaded with real data, the amount of
> fetched records will be too big down the road.
>
> Maybe we are too much worried on the other hand. What I was looking for is
> to get the picture about the size of the DataTable used by other, more
> experienced database developers.
>
> In our case I think the disconnected datatable will not have more than 100
> 000 records with 8-10 columns - in teh worst scenario. Most probably it
> will
> be much less.
>
> Regards,
> Lubomir
>
>
>
> "Miha Markic" wrote:
>
>> As Cor said, you should load only data you need for a certain process,
>> process it and store it back. There is no need to keep enormous amounts
>> of
>> data in DataSet.
>> --
>> 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/
>>
>>

Cor
Thu Oct 18 21:08:55 PDT 2007

Lubomir,

There is no reasonable answer for this then that you can self better
calculate. If there is one column with real images in it, then it will be
sure much more then that there are only 1.000 columns which can contain one
integer.

Cor

Lubomir
Mon Oct 22 15:00:01 PDT 2007

Hi,

Thanks for all answers. I wanted to get the feeling about "what is
standard/usuall size and what seems to be too much". I think "I have that
feeling ".

Thanks,

Lubomir



"Lubomir" wrote:

> Hi,
> Thanks for answers.
>
> Yes, we are using filters to fetch the data from database. However we are
> concerned that when database will be loaded with real data, the amount of
> fetched records will be too big down the road.
>
> Maybe we are too much worried on the other hand. What I was looking for is
> to get the picture about the size of the DataTable used by other, more
> experienced database developers.
>
> In our case I think the disconnected datatable will not have more than 100
> 000 records with 8-10 columns - in teh worst scenario. Most probably it will
> be much less.
>
> Regards,
> Lubomir
>
>
>
> "Miha Markic" wrote:
>
> > As Cor said, you should load only data you need for a certain process,
> > process it and store it back. There is no need to keep enormous amounts of
> > data in DataSet.
> > --
> > 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/
> >
> >