What is the best way to get records from Datatable?

Question

I have a table which contains approx. 150000 records. It could be change.. I mean more.. I don't know. And It's an access db (its an obligation) I use .net framework 3.0

vb.net or c# don't matter..

I used that code

    con = New OleDbConnection("provider=microsoft.jet.oledb.4.0; data source=db.mdb")
    cmd = New OleDbCommand("Select * from TAPU_GEMLIK", con)
    da = New OleDbDataAdapter(cmd)

    ds = New DataSet()
    da.Fill(ds)
    DgKayitlar.DataSource = ds.Tables(0)

Well, my program will apply some filtres to records.

I have two questions

1-

    da.Fill(ds)
    DgKayitlar.DataSource = ds.Tables(0)

in here, the program waits for a while. If records increas,it will wait for much. What can I use for it? I thought that I can use thread, but I am not sure.

2- each filters queries database. filter means just a condition, such as

where Name=..... and Surname... etc.

is it a good way to query database or I must get result of query from gridview? Which could be faster or better?

Edit: I can't paging. because program will already search each records from database. firstly, I need to get all records from db.

You might want to consider implementing "infinite scrolling" as you can't use paging. — Andrew Morton, Aug 05 '12 at 16:06
is there anyway to get values part by part? I am not programming a web app. — unbalanced, Aug 05 '12 at 16:11
If you use a DataGridView, you can use virtual mode ( http://msdn.microsoft.com/en-us/library/cd28yf6d ) which enables you to make it only perform the layout for the items in view. — Andrew Morton, Aug 05 '12 at 16:26

score 0 · Answer 1 · answered Aug 05 '12 at 15:35

0

If the filters are simple where Name=..... and Surname... etc.

I would suggest you go for option 2 but do the filtering in sql query you have Select * from TAPU_GEMLIK

answered Aug 05 '12 at 15:35

HatSoft

11,077
3
28
43

yes filters will be simple but contains a few conditions.. max 6. and thank you for suggestion – unbalanced Aug 05 '12 at 15:37
can you provide a bit more explanation to why you cant use pagination for resolving Q1 ? – Umair Khan Aug 05 '12 at 15:40
@HarunAbi try getting top 6 in your sql query – HatSoft Aug 05 '12 at 15:41
@UmairKhan, users must see all resold in one page and after they must search some record with some filters.. If will be paging, its not more sense. I think I cant explain it .. its really difficult to say what I mean :) but I musnt use it – unbalanced Aug 05 '12 at 15:48
@HatSoft, you dont understand what i meant, I wanted to say that conditions' count will be max 6. such as where name .. surname, fathername, street.. somenumbers.. – unbalanced Aug 05 '12 at 15:49

score 0 · Answer 2 · answered Aug 05 '12 at 15:57

If you must load all data in one go from database then i can suggest caching result set from you query ( apart from optimizing the DB ofcourse ) as it would cut down on your result set loading time. But this is only advantageous if the data doesn't change very often. Furthermore, if all the data is already rendered to the page then i think it makes not sense querying the database again for searching. Using grid's search option would be better and faster. However i must tell you that loading huge amount of data on a page will squeeze the life out of browser and you may face issues such as unresponsiveness.

thanx, its not a web app and data of db never change.my project just searches and show results on datagridview.. — unbalanced, Aug 05 '12 at 16:01

score 0 · Answer 3 · answered Aug 06 '12 at 10:25

0

You need to learn more about how to query for data using SQL.

For example, do you need ALL of the columns from the database? Or do you only need a couple. If you only need 6 results use the TOP command e.g. "Select Top 6".

More columns/rows you select the more data needs to be transferred. The more data being moved the slower the query will appear.

Do the filtering on the server. If your data will always be filtered on particular column use an Index. Indexes will increase the filtering performance massively.

The key to efficant queries is to filter on indexed columns and only return what you need.

answered Aug 06 '12 at 10:25

pingoo

2,074
14
17

I think I couldnt explain very well :) results would be more than 150.000.. I meant that 6 is condtions.they are parameters of "where". such as; where name='....' and surname='....' + .... ..... these conditions are 6. but records are more than than 150000. then I dont need to use "top command" .. thank you for attention :) – unbalanced Aug 06 '12 at 21:41
In that case have a look at Dynamic where clauses (http://www.codeproject.com/Articles/21234/Implementing-Dynamic-WHERE-Clause-in-Static-SQL). You'll also want to implement a paging type arrangement where as the user scrolls down the app continues to load more rows, like how FaceBook works. – pingoo Aug 07 '12 at 08:19

What is the best way to get records from Datatable?

3 Answers3