Q: Tip and advice on DataBase related question.

[Martin Arvidsson]

Hi!

I am going to build my firs Windows Application that involves a Database.

Now this database and its tables are quite large so i got a couple of quizes
regarding performance etc just to get me started...

One of the tables includes over a milion customers. This is not a great idea
to read in to a dataset i figured out, the hard way i might ad. So i created
a DataGridView wich fills the top 250 customers. Now for the tricky part
that i just cant figure out on how to do. when i scroll down, i would like
read the next 250 customers. Dont know if this is a good idea. Because how
is performnace when you reach that milion'th customer and the grid is
complete with 1 milion customers? Any tips, code directions etc to guide me?

This is my initial question for now. I will surly have more in the future.

Thanx in advance

regards
Martin

 

[Marc Gravell]

Tips? Yes: don't try to display a million rows in a grid. Even in
"virtual" mode, what is the use? It sounds to me that what you really
want to be writing is a dynamic search screen, that brings back (as you
rightly started) paged data; 250 rows is a good size for a page.

But start off with *something*. Even the most insane manager isn't
going to scroll through 1M rows... let them enter the name, post [zip]
code, order number, "contacted between dates", *something* (refer to
the requirements document... OK... first gather requirements ;-p). You
could even use regex to identify the pattern to make for better
searching (looks like a zip; search on zip; looks like an order number;
search via orders; else search by name, etc).

Also; with large data, don't try and hold it in memory all at once
(unless you have Win64 and a great pile of memory and a fast network
*to yourself* and a SAN and (you get the idea; don't do it!);
DataReader might be worth a look (using custom objects rather than
DataSets), or you could just keep the data volume down.

Marc

 

[Duggi]

TIP:: I have one....

What I understood is that you want to display data on to a view, the
data is very bulk...Umm.. 1million...

In this case I prefer avoiding display all the data, but give the user
a choice of search by a field ...... would be better option....

If you still want to display all the data,
1. create a dataview and read the top 250 rows and display
2. If user clicks or scrolls down for next data, flush the previous
data and query database for the next 250 rows, and on... (Yes you may
need to maintain the index on your form)

By flushing the previous data, at any given point of time there will
not be more than 250 rows in memory.
As far as performance is concerned, for storing 1million db rows, I
assume you have a strong DB server. which will give you 250 rows in
fraction of time. Also run the query statement in a separate thread, so
that user will not get hour glass on winform...

Thanks
-CNU.