other table that stores data differently than ADO.Net datatable?

[VMI]

We have this huge application that's based on storing tons of data on a
dataTable. The only problem we're having is that storing LOTS of data (1
million records) into a datatable will slow down the system since all this
data will be stored in memory. The performance is really affected with this.
Since we don't really want to redesign everything based on this datatable,
are there any companies that offer a product similar to the datatable that
stores lots of data in a more efficient way? Basically, we load all this
data (it's a one-shot process) into the datatable and work with that. We
don't want to change any of that; we just want something similar to the
datatable that stores a better way. Also, the windows datagrid should be
able to bind to this new table without many problems.
Thanks.

[Nicholas Paldino [.NET/C# MVP]]

VMI,

Granted, you don't want to redesign your app, or how you store the data,
but the DataTable as it exists is as efficient as you are probably going to
get (as well as standard). Basically, it's a hash mechanism (to get the
values of the columns at a particular row).

On top of that, there is little that can be done to improve it.

While the data that you are working with is static, having one million
rows at one time is a design flaw, in my opinion. Also, I can't imagine any
reason why you would need to bind a data grid to a table with one million
rows in it. There is no way for a human to process all of this data at one
time, and having it all up front isn't helping you obviously, because you
are facing performance hits due to the massive amount of memory taken up.

I would recommend that you work with this data in smaller sets, getting
it from a persistant store (i.e. a database) when needed.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"VMI" <vo******@yahoo.com> wrote in message
news:uM**************@TK2MSFTNGP15.phx.gbl...

We have this huge application that's based on storing tons of data on a
dataTable. The only problem we're having is that storing LOTS of data (1
million records) into a datatable will slow down the system since all this
data will be stored in memory. The performance is really affected with
this.
Since we don't really want to redesign everything based on this datatable,
are there any companies that offer a product similar to the datatable that
stores lots of data in a more efficient way? Basically, we load all this
data (it's a one-shot process) into the datatable and work with that. We
don't want to change any of that; we just want something similar to the
datatable that stores a better way. Also, the windows datagrid should be
able to bind to this new table without many problems.
Thanks.

[Lateralus [MCAD]]

I think that no matter what you use it will be a memory hog. If you're
loading that much data into memory I think you'll run into performance
issues. Is there any reason you just don't load what you need from the DB
instead of loading everything?

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:uM**************@TK2MSFTNGP15.phx.gbl...

We have this huge application that's based on storing tons of data on a
dataTable. The only problem we're having is that storing LOTS of data (1
million records) into a datatable will slow down the system since all this
data will be stored in memory. The performance is really affected with
this.
Since we don't really want to redesign everything based on this datatable,
are there any companies that offer a product similar to the datatable that
stores lots of data in a more efficient way? Basically, we load all this
data (it's a one-shot process) into the datatable and work with that. We
don't want to change any of that; we just want something similar to the
datatable that stores a better way. Also, the windows datagrid should be
able to bind to this new table without many problems.
Thanks.

[VMI]

Thanks for your replies.
We were initially working with 10,000-15,000 records and we never imagined
that these records would reach 1 million, so it's really something we didn't
think through very well (aka a design flaw).
We've already migrated the data to database but we don't exactly know how to
query the database everytime the user does a scroll (or page up/page down)
in the grid. What event should we capture in order to query the DB again and
fill the table? Also, I assume we need to create a Select statement that
will display the next N records once the user has reached the last visible
record in the grid/datatable (in pseudocode: "Select the next N records from
table following the last record displayed"). That's what we haven't been
able to figure out.

Any help is appreciated.
"VMI" <vo******@yahoo.com> wrote in message
news:uM**************@TK2MSFTNGP15.phx.gbl...

We have this huge application that's based on storing tons of data on a
dataTable. The only problem we're having is that storing LOTS of data (1
million records) into a datatable will slow down the system since all this
data will be stored in memory. The performance is really affected with this. Since we don't really want to redesign everything based on this datatable,
are there any companies that offer a product similar to the datatable that
stores lots of data in a more efficient way? Basically, we load all this
data (it's a one-shot process) into the datatable and work with that. We
don't want to change any of that; we just want something similar to the
datatable that stores a better way. Also, the windows datagrid should be
able to bind to this new table without many problems.
Thanks.

[Lateralus [MCAD]]

I think you may want to take another approach. Are there any fields you can
filter on? For instance you may have a foreign key to a lookup type table. I
would suggest making some sort of a "search" type of screen and filter your
records that way. Just dynamically build a SQL statement using the search
criteria for your where clause, that will help slim down your list. If your
data is not normalized this may be a bit difficult. You can page your
results with a DataGrid in ASP.Net, but I don't think you can with winforms.

HTH

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP15.phx.gbl...

Thanks for your replies.
We were initially working with 10,000-15,000 records and we never imagined
that these records would reach 1 million, so it's really something we
didn't
think through very well (aka a design flaw).
We've already migrated the data to database but we don't exactly know how
to
query the database everytime the user does a scroll (or page up/page down)
in the grid. What event should we capture in order to query the DB again
and
fill the table? Also, I assume we need to create a Select statement that
will display the next N records once the user has reached the last visible
record in the grid/datatable (in pseudocode: "Select the next N records
from
table following the last record displayed"). That's what we haven't been
able to figure out.

Any help is appreciated.
"VMI" <vo******@yahoo.com> wrote in message
news:uM**************@TK2MSFTNGP15.phx.gbl...

We have this huge application that's based on storing tons of data on a
dataTable. The only problem we're having is that storing LOTS of data (1
million records) into a datatable will slow down the system since all
this
data will be stored in memory. The performance is really affected with

this.

Since we don't really want to redesign everything based on this
datatable,
are there any companies that offer a product similar to the datatable
that
stores lots of data in a more efficient way? Basically, we load all this
data (it's a one-shot process) into the datatable and work with that. We
don't want to change any of that; we just want something similar to the
datatable that stores a better way. Also, the windows datagrid should be
able to bind to this new table without many problems.
Thanks.

[VMI]

But there may be a possibility that a certain query will return 500K
records. In that case, I'd need to load those 500K records into the table.
"Lateralus [MCAD]" <dnorm252_at_yahoo.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

I think you may want to take another approach. Are there any fields you can filter on? For instance you may have a foreign key to a lookup type table. I would suggest making some sort of a "search" type of screen and filter your records that way. Just dynamically build a SQL statement using the search
criteria for your where clause, that will help slim down your list. If your data is not normalized this may be a bit difficult. You can page your
results with a DataGrid in ASP.Net, but I don't think you can with winforms.
HTH

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP15.phx.gbl...

Thanks for your replies.
We were initially working with 10,000-15,000 records and we never imagined that these records would reach 1 million, so it's really something we
didn't
think through very well (aka a design flaw).
We've already migrated the data to database but we don't exactly know how to
query the database everytime the user does a scroll (or page up/page down) in the grid. What event should we capture in order to query the DB again
and
fill the table? Also, I assume we need to create a Select statement that
will display the next N records once the user has reached the last visible record in the grid/datatable (in pseudocode: "Select the next N records
from
table following the last record displayed"). That's what we haven't been
able to figure out.

Any help is appreciated.
"VMI" <vo******@yahoo.com> wrote in message
news:uM**************@TK2MSFTNGP15.phx.gbl...

We have this huge application that's based on storing tons of data on a
dataTable. The only problem we're having is that storing LOTS of data (1 million records) into a datatable will slow down the system since all
this
data will be stored in memory. The performance is really affected with

this.

Since we don't really want to redesign everything based on this
datatable,
are there any companies that offer a product similar to the datatable
that
stores lots of data in a more efficient way? Basically, we load all this data (it's a one-shot process) into the datatable and work with that. We don't want to change any of that; we just want something similar to the
datatable that stores a better way. Also, the windows datagrid should be able to bind to this new table without many problems.
Thanks.

[Lateralus [MCAD]]

Well, there is always that possibility with any search screen. The more
criteria they enter, the more specific the results are. Take Google for
instance. If you search on "C#" you'll get too many hits. If you search on
"C# DataSet XML" your list will be more specific to what you are looking
for. It is up to the user how "filtered" the list will be.

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

But there may be a possibility that a certain query will return 500K
records. In that case, I'd need to load those 500K records into the table.
"Lateralus [MCAD]" <dnorm252_at_yahoo.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

I think you may want to take another approach. Are there any fields you

can

filter on? For instance you may have a foreign key to a lookup type
table.

I

would suggest making some sort of a "search" type of screen and filter

your

records that way. Just dynamically build a SQL statement using the search
criteria for your where clause, that will help slim down your list. If

your

data is not normalized this may be a bit difficult. You can page your
results with a DataGrid in ASP.Net, but I don't think you can with

winforms.

HTH

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP15.phx.gbl...

> Thanks for your replies.
> We were initially working with 10,000-15,000 records and we never imagined > that these records would reach 1 million, so it's really something we
> didn't
> think through very well (aka a design flaw).
> We've already migrated the data to database but we don't exactly know how > to
> query the database everytime the user does a scroll (or page up/page down) > in the grid. What event should we capture in order to query the DB
> again
> and
> fill the table? Also, I assume we need to create a Select statement
> that
> will display the next N records once the user has reached the last visible > record in the grid/datatable (in pseudocode: "Select the next N records
> from
> table following the last record displayed"). That's what we haven't
> been
> able to figure out.
>
> Any help is appreciated.
>
>
> "VMI" <vo******@yahoo.com> wrote in message
> news:uM**************@TK2MSFTNGP15.phx.gbl...
>> We have this huge application that's based on storing tons of data on
>> a
>> dataTable. The only problem we're having is that storing LOTS of data (1 >> million records) into a datatable will slow down the system since all
>> this
>> data will be stored in memory. The performance is really affected with
> this.
>> Since we don't really want to redesign everything based on this
>> datatable,
>> are there any companies that offer a product similar to the datatable
>> that
>> stores lots of data in a more efficient way? Basically, we load all this >> data (it's a one-shot process) into the datatable and work with that. We >> don't want to change any of that; we just want something similar to
>> the
>> datatable that stores a better way. Also, the windows datagrid should be >> able to bind to this new table without many problems.
>>
>>
>> Thanks.
>>
>>
>
>

[Nicholas Paldino [.NET/C# MVP]]

I have to ask, what human can possibly process 500K records at one time?
Think of things like paging, etc, etc, to cut down on the result set and
then display that. I would definitely say that this is the kind of scenario
where some out of the box thinking would be a good thing.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

But there may be a possibility that a certain query will return 500K
records. In that case, I'd need to load those 500K records into the table.
"Lateralus [MCAD]" <dnorm252_at_yahoo.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

I think you may want to take another approach. Are there any fields you

can

filter on? For instance you may have a foreign key to a lookup type
table.

I

would suggest making some sort of a "search" type of screen and filter

your

records that way. Just dynamically build a SQL statement using the search
criteria for your where clause, that will help slim down your list. If

your

data is not normalized this may be a bit difficult. You can page your
results with a DataGrid in ASP.Net, but I don't think you can with

winforms.

HTH

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP15.phx.gbl...

> Thanks for your replies.
> We were initially working with 10,000-15,000 records and we never imagined > that these records would reach 1 million, so it's really something we
> didn't
> think through very well (aka a design flaw).
> We've already migrated the data to database but we don't exactly know how > to
> query the database everytime the user does a scroll (or page up/page down) > in the grid. What event should we capture in order to query the DB
> again
> and
> fill the table? Also, I assume we need to create a Select statement
> that
> will display the next N records once the user has reached the last visible > record in the grid/datatable (in pseudocode: "Select the next N records
> from
> table following the last record displayed"). That's what we haven't
> been
> able to figure out.
>
> Any help is appreciated.
>
>
> "VMI" <vo******@yahoo.com> wrote in message
> news:uM**************@TK2MSFTNGP15.phx.gbl...
>> We have this huge application that's based on storing tons of data on
>> a
>> dataTable. The only problem we're having is that storing LOTS of data (1 >> million records) into a datatable will slow down the system since all
>> this
>> data will be stored in memory. The performance is really affected with
> this.
>> Since we don't really want to redesign everything based on this
>> datatable,
>> are there any companies that offer a product similar to the datatable
>> that
>> stores lots of data in a more efficient way? Basically, we load all this >> data (it's a one-shot process) into the datatable and work with that. We >> don't want to change any of that; we just want something similar to
>> the
>> datatable that stores a better way. Also, the windows datagrid should be >> able to bind to this new table without many problems.
>>
>>
>> Thanks.
>>
>>
>
>

[VMI]

If I were to do it this way (display data after being queried), there's no
guarantee that a user will not create a query that returns 500,000 records.
Since this is address data (each record is a physical,PO Box, residential
address) and the user decides he/she wants to view all the possible
addresses in NY, this query would return at least 500K addresses for the
user to view. The user may only look at 5 addresses but the 500k records
would still need to be there. That's where I'd need to create some other way
to query the table by telling it that, of the 500K records that the initial
query affects, only return records 1-100 (first 100) to the datatable. When
the user scrolls, or does a page down, run a query saying to bring in the
next 100 records (101-200) of the initial 500K query, and so on.
I know I'm exagerating a bit because most tables will contain 300K-500K of
addresses for a state, but the user, as impossible as it may be, may want to
look at all the records.

What would you guys suggest?
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:O0**************@TK2MSFTNGP15.phx.gbl...

I have to ask, what human can possibly process 500K records at one time? Think of things like paging, etc, etc, to cut down on the result set and
then display that. I would definitely say that this is the kind of scenario where some out of the box thinking would be a good thing.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

But there may be a possibility that a certain query will return 500K
records. In that case, I'd need to load those 500K records into the table.

"Lateralus [MCAD]" <dnorm252_at_yahoo.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

I think you may want to take another approach. Are there any fields you

can

filter on? For instance you may have a foreign key to a lookup type
table.

I

would suggest making some sort of a "search" type of screen and filter

your

records that way. Just dynamically build a SQL statement using the search criteria for your where clause, that will help slim down your list. If

your

data is not normalized this may be a bit difficult. You can page your
results with a DataGrid in ASP.Net, but I don't think you can with

winforms.

HTH

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP15.phx.gbl...
> Thanks for your replies.
> We were initially working with 10,000-15,000 records and we never

imagined

> that these records would reach 1 million, so it's really something we
> didn't
> think through very well (aka a design flaw).
> We've already migrated the data to database but we don't exactly know

how

> to
> query the database everytime the user does a scroll (or page up/page

down)

> in the grid. What event should we capture in order to query the DB
> again
> and
> fill the table? Also, I assume we need to create a Select statement
> that
> will display the next N records once the user has reached the last

visible

> record in the grid/datatable (in pseudocode: "Select the next N records > from
> table following the last record displayed"). That's what we haven't
> been
> able to figure out.
>
> Any help is appreciated.
>
>
> "VMI" <vo******@yahoo.com> wrote in message
> news:uM**************@TK2MSFTNGP15.phx.gbl...
>> We have this huge application that's based on storing tons of data on >> a
>> dataTable. The only problem we're having is that storing LOTS of data

(1

>> million records) into a datatable will slow down the system since

all >> this
>> data will be stored in memory. The performance is really affected with > this.
>> Since we don't really want to redesign everything based on this
>> datatable,
>> are there any companies that offer a product similar to the datatable >> that
>> stores lots of data in a more efficient way? Basically, we load all

this

>> data (it's a one-shot process) into the datatable and work with

that. We

>> don't want to change any of that; we just want something similar to
>> the
>> datatable that stores a better way. Also, the windows datagrid

should be

>> able to bind to this new table without many problems.
>>
>>
>> Thanks.
>>
>>
>
>

[Lateralus [MCAD]]

Since winforms doesn't have a datagrid that has "paging" to my knowledge.
You may want to look into inheriting from DataGrid, or creating a
usercontrol with a datagrid on it, then add your own custom functionality.
e.g., << previous page, next page >> type of buttons/links on it. Obviously
adding the custom functionality will not be the easiest thing to do, but I'm
sure it can be done. Probably the easiest thing to do would be to limit the
number of records you show in the grid. As you said before 1 - 100, then
101 - 200 etc....

--
Lateralus [MCAD]
"VMI" <vo******@yahoo.com> wrote in message
news:uK**************@tk2msftngp13.phx.gbl...

If I were to do it this way (display data after being queried), there's no
guarantee that a user will not create a query that returns 500,000
records.
Since this is address data (each record is a physical,PO Box, residential
address) and the user decides he/she wants to view all the possible
addresses in NY, this query would return at least 500K addresses for the
user to view. The user may only look at 5 addresses but the 500k records
would still need to be there. That's where I'd need to create some other
way
to query the table by telling it that, of the 500K records that the
initial
query affects, only return records 1-100 (first 100) to the datatable.
When
the user scrolls, or does a page down, run a query saying to bring in the
next 100 records (101-200) of the initial 500K query, and so on.
I know I'm exagerating a bit because most tables will contain 300K-500K of
addresses for a state, but the user, as impossible as it may be, may want
to
look at all the records.

What would you guys suggest?
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:O0**************@TK2MSFTNGP15.phx.gbl...

I have to ask, what human can possibly process 500K records at one

time?

Think of things like paging, etc, etc, to cut down on the result set and
then display that. I would definitely say that this is the kind of

scenario

where some out of the box thinking would be a good thing.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

> But there may be a possibility that a certain query will return 500K
> records. In that case, I'd need to load those 500K records into the table. >
>
> "Lateralus [MCAD]" <dnorm252_at_yahoo.com> wrote in message
> news:Oa**************@TK2MSFTNGP11.phx.gbl...
>> I think you may want to take another approach. Are there any fields
>> you
> can
>> filter on? For instance you may have a foreign key to a lookup type
>> table.
> I
>> would suggest making some sort of a "search" type of screen and filter
> your
>> records that way. Just dynamically build a SQL statement using the search >> criteria for your where clause, that will help slim down your list. If
> your
>> data is not normalized this may be a bit difficult. You can page your
>> results with a DataGrid in ASP.Net, but I don't think you can with
> winforms.
>>
>> HTH
>>
>> --
>> Lateralus [MCAD]
>>
>>
>> "VMI" <vo******@yahoo.com> wrote in message
>> news:Oh**************@TK2MSFTNGP15.phx.gbl...
>> > Thanks for your replies.
>> > We were initially working with 10,000-15,000 records and we never
> imagined
>> > that these records would reach 1 million, so it's really something
>> > we
>> > didn't
>> > think through very well (aka a design flaw).
>> > We've already migrated the data to database but we don't exactly
>> > know
> how
>> > to
>> > query the database everytime the user does a scroll (or page up/page
> down)
>> > in the grid. What event should we capture in order to query the DB
>> > again
>> > and
>> > fill the table? Also, I assume we need to create a Select statement
>> > that
>> > will display the next N records once the user has reached the last
> visible
>> > record in the grid/datatable (in pseudocode: "Select the next N records >> > from
>> > table following the last record displayed"). That's what we haven't
>> > been
>> > able to figure out.
>> >
>> > Any help is appreciated.
>> >
>> >
>> > "VMI" <vo******@yahoo.com> wrote in message
>> > news:uM**************@TK2MSFTNGP15.phx.gbl...
>> >> We have this huge application that's based on storing tons of data on >> >> a
>> >> dataTable. The only problem we're having is that storing LOTS of data > (1
>> >> million records) into a datatable will slow down the system since all >> >> this
>> >> data will be stored in memory. The performance is really affected with >> > this.
>> >> Since we don't really want to redesign everything based on this
>> >> datatable,
>> >> are there any companies that offer a product similar to the datatable >> >> that
>> >> stores lots of data in a more efficient way? Basically, we load all
> this
>> >> data (it's a one-shot process) into the datatable and work with that. > We
>> >> don't want to change any of that; we just want something similar to
>> >> the
>> >> datatable that stores a better way. Also, the windows datagrid should > be
>> >> able to bind to this new table without many problems.
>> >>
>> >>
>> >> Thanks.
>> >>
>> >>
>> >
>> >
>>
>>
>
>