Datareader or Dataset in Winform with long trips to database?

Question

I've got a Winform app that will be used in the US and China. The SQL Server 2005 database is in the US, so the data access is going to be slower for the people in China. I'm deciding between using a DataReader and a Dataset for best performance. The data will immediately be loaded into business objects upon retrieval.

Question: Which performs better (DataReader/DataSet) pulling data from a database that's far away? I have read that the DataReader goes back to the database for each .Read(), so if the connection is slow to begin with, will the DataSet be a better choice here?

Thanks

Answer 1

Hi,

depend of amount of data. You cannot store in memory (dataset) a too large amount.

Two ways for your problem at my mind :
- parallelisation (System.Thread)
- backgroundworkers

The first can improve performance in linq to sql cases. The second can help end users have a better experience (non bloqued UI).

Answer 2

The performance of datareader vs a dataset will barely be measurable compared to the database roundtrips if you're expecting long distance/slow links.

DataSets might use more memory though, which might be a concern if you're dealing with a lot of data.

Answer 3

I think it doesn't matter since the connection is the bottleneck.

Both use the same mechanism to fetch the data (ADO.NET/Datareader).

If you can do it you might compress the query result on the server en THEN send it to the client. That would improve performance.

Answer 4

Depends on what database it is. Bad situation if it is Access.
Depends on how much data is moved around, what is the usage style? will users from China just read or do read/write on common data? do they need to see all data?

The idea is, separate the data (if it helps the scenario) and merge it back.

Answer 5

It doesn't matter which you choose since the DataSet uses the DataReader to fill. Try to avoid calling the db whereever possible, by caching results or by getting extra data. A few calls that get extra data will probably outperform alot of small pecks at the tables. Maybe a BackgroundWorker could preload some data that you know you will be using.

Answer 6

Just for other readers: the DataReader is MUCH more performant. Obviously, these users have not tried using both and actually tested the difference. Load 1,000 records with a DataReader and 1,000 with a DataSet. Then try limiting the records for the DataSet to 10 records (using the adapter's Fill method so that the 1,000 are loaded, but only 10 are populated/filled into the DataSet).

I really don't know why DataSets are so bad performance-wise during the fill operation, but the difference is huge. Its much faster to create your own collection and fill them with a DataReader than use the very bloated and slow DataSet.