Accessing huge data from Remote Server in C#

Question

Hi,

I have a database based in NY and my HK users are trying to get the data from this database. The data retrieved is approximately 20 mb (if you save the Datatable as csv the file size is 20 mb). It is taking around 20 mins for the data to come.

I am using SQL Server 2005 as database and c# as desktop application. I cannot have a separate database for HK Users due to budget constraints.

Can anyone please suggest a way to bring the retrieval time to around 5 mins?

Regards,

Pavan

Answer 1

Try retrieving only the data you need. You may be able to do this by writing a web service to return the appropriate data, though you'd need to make sire it was secure.

Alternatively, install a SQL Server Express database in HK - it's free and will support up to 4GB of data, though you would have to work out some way to replicate the data so it matches the 2 environments.

It seems excessive to return the full 20MB of data from the database in one request. Presumably there's a good reason for it?

Answer 2

Are you trying to get the entire database or something? If not, I'd recommend paging i.e. retrieving say 100 records at a time.

Answer 3

Just a thought, have you tried GZIPping the data as it goes over the wire? I find that SQL dumps compress particularly well, sometimes up to 90%. Of course it depends how you are doing the query as to how easy that will be.

If you are doing a straight SQL query from HK over the internet to the database server in NY that's probably not a great idea anyway, you could write a wrapper that does the query and then compresses the result before transfer to the remote site.

Answer 4

Couple of thoughts;

1. 20Mb in 20mins is about 17kb/s which is extremely slow - are there other points in your process adding to the latency?
2. In terms of a possible solution, perhaps consider using an HTTP endpoint and enable gzip compression. This will no longer be an ado.net connection but your data size should come down significantly and depending on the structure of your app may be quite simple to implement. More info here.

Good luck!

Answer 5

We're using a CompressedDataSet that's derived of dataset. It has two added functions:

public string GetCompressedDataSet()
public void SetCompressedDataSet(string data)

These function (de)serialize the dataset to XML and then use GZip to (de)compress. Compressing your datasets goes a long way.

Of course, do you really need the 20 mb data? It might be an option to limit the data you request. Or split it up: ask 10x2 mb and update your application every 2 minutes.

Answer 6

The only thing I have to add is to make sure you are setting the RemotingFormat to Binary. Not sure if you can get away with just setting it on your DataSet or if you need to on all the DataTables also. As a habit I always set both to binary.

It is my experience that a DataTable of data will serialize to a smaller size than say a comparable strongly typed array of some corresponding object.

Oh, one last thing. You will save a little more space if you can set the AllowNull to false on columns that don't need it.