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.

 

[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?

 

[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.

 

[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

 

[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.

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]

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.

 

[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]

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.

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]

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.


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]
- (e-mail address removed)

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.

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]

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.


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?

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]
- (e-mail address removed)

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.

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]


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.


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]

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?


in
message news:[email protected]...

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]
- (e-mail address removed)

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


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.


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.