Working with huge database

eli

19y3kLearn iOS Programming

Hi :)

I have a database that contain many many records (like 100,000 or so).

And i want to load and present them in a datagrid.

The problem is that loading all the records and presenting them on a datagrid is very slow,

and i am looking for a way to do that fast, is there a way to do that fast ?

I fought about presenting each time only a caple of records, and when the user scroll

down the datagrid, more records are loaded to it.

The problem is that i dont really succeded on doing that good as i expected :\

Ill be happy for suggestions on how to do that, or any other idea on how to make it fast.

Thanks :)

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

eli19y ago
Hi :)

Im using pervasive.sql database, its a windows application, and i have

an index, but i need to get it in another order then the index.

What should i do ? :(
0
Alistair Thrussell19y ago
What kind of database are you running (Sql Server, Oracle, MySQL....) I'm hoping you're not using some crappy flat file based database. 100 000 records is not big. Has the table you're hitting got indexs on it? Are you hitting the indexes? Is this a web or windows app? You really shouldn't have hassles getting 500 records at a time.
0
eli19y ago
Hi :) r

Thanks for the answer.

I tryed to do what you sayd, and it still pretty slow :(

The load of 500 houndred rows is slow because i need to get them in order,

so in the SQL i add 'ORDER BY name' which makes it much slower :\

So i took 100 and not 500, but its still pretty slow, and the load of each chunk is slow,

its taking like caple of seconds to load each chunk, and its not good for me.

(when i move the 'ORDER BY' it makes it much more faster, but i need that ORDER BY )

What should i do ?

Thanks.
0
Mahesh Chand19y ago
You can use SqlDataAdapter's Fill method to specify what range of records do you want to retrieve from the database.
```
public int Fill (
	DataSet dataSet,
	int startRecord,
	int maxRecords,
	string srcTable
)
```
Where:

dataSet : A DataSet to fill with records and, if necessary, schema.
startRecord : The zero-based record number to start with.
maxRecords : The maximum number of records to retrieve.
srcTable : The name of the source table to use for table mapping.

If you want to keep previous records in the DataSet, you can use DataSet.Merge feature to merge new records with current records. So you will need two DataSets. One will store all data and be bound to the data-bound controls and second will be used to get the latest data and them second will merge into first DataSet.
0
eli19y ago
Hi :)

Thanks for the answer.

I see, but how can i do that:

"you send a request to the database and get records from 501 - 1000 and update your DataSet object in memory with this new data."

1)What is the SQL command to take records from the X place to Y place ?

I couldnt find somthing that do that :\

2) How do i add the new records to the DataSet without erasing the previous records ?

3) What will happen if the user will get to the 450# ? suddenly the software will stop, for a while, i think it will be a little bit annoying, isnt it ?

Thanks.
0
Mahesh Chand19y ago
That's the way to go. You get records in chunks based on your current records selected on the UI. Depending on the size of your screen, say if you display 50 records per screen, you may get 500 records per data request. So when user passes on records after 450, you send a request to the database and get records from 501 - 1000 and update your DataSet object in memory with this new data.

If your application is Windows Forms application, that means, a single user is using at one machine, you may get data in 1000s chunks but if application is a Web application, 500 or less is recommended b/c each user will have it's own set of data. For example, one user may be looking at record 11,200 and other user may be looking at record number 301. So you have to be smart to store latest 500 or less records in session state.
0