what's the best way to load loads of data (all about performance)

Question

Hi,

We got a database server with a big database. Now we must develop a program which can show all the data.

There are only two requirements:

• When the connection to the database server is lost the program should be able to continue.
• Performance while the program is running (The loading time for startup does not matter).

What's the best way to do this? Please some advice or samples.

Kind regards,

Flaminio

Answer 1

When it comes to speed, DataReaders come tops.

---

Only performance counts!

Answer 2

Flaminio,

if you need to resume the data retrieval if you lose connection then you will need to keep track of the last record returned successfully and have a way to identify the next record to start a new select with.  typically an auto generated id column is used and the id would be incremented automatically with each insert and so if you need to select again, you can get the records where the id is greater than the last successfull returned record id.  hope that makes sense.

you could use a stored procedure or just pass in a select from your application using a command.  and as Sylva suggested, you can use a datareader to read the returned data.  as you read the returned records one at a time, you can create a list of object with objects built from each record.  or, you can build datarows and add them to a datatable.

hope this helps

---

FREE
DEVELOPER TOOLS     CODE     PROJECTS
DATABASE CODE GENERATOR
DATABASE / GENERAL  APPLICATION TUTORIAL
Upload Projects to share or get help on and post the generated links here in the forum
www.srsoft.us

Answer 3

Hi Sylva and Jeff,

Thank you for such great information. I like the idea of tracking records only there is one problem. When the program starts and there is no connection with the database server there is no data to load. Can we do this through a local subset of the database?

Kind regards,

Flaminio

Answer 4

Flaminio,

you can use 'data caching'. If your data is not so dynamic, i mean its not going to change frequently using local db is more efficient. You can easily deal with connection lost situation. Whenever you have the connection, make sure you have latest data updated in local db. Make your application read from local db. Even if there is no connection from db server, you still start the application with cached data.

 

---

Thanks Mike --------Please mark as answer if it is useful----------

Answer 5

Hi Mike,

The scenario is a little changed now and is as follow: The database server is now part of our intranet instead of the Internet. What is the best way to speed up our clients?

1. Create lists of objects while loading the application (splash screen) and get the correct data with LinQ?
   (I am not in favor of LINQ because there is no index, so i think it is slower as a database query)
2. Read data directly from our database with the datareader?
3. Other options?

Kind regards,

Flaminio

Answer 6

Reading data directly with reader definitely give you much better performance, and will avoid troubles using disconnected data source. But if you have 1000’s rows then better stay with data caching.

---

Thanks Mike --------Please mark as answer if it is useful----------