How to speed up saving data to database over the internet?

Question

I am using C#, Entity Framework and SQL Server 2008 Express.
I am connected to the database over the internet.
What is the best method to speed up saving/updating data to database?
It takes 87 seconds to save 180 records to the database.
It takes only 3.5 seconds to save the same number of records to local (on the same machine) SQL Server.

For every record that I save/update I check if record with this primary key exists in the database,
if it doesn't exists I simply save it,
if it exists I update it using ObjectContext.ApplyCurrentValues(entitySetName, currentEntity) method.

Answer 1

I would consider sending a string similar to CSV with all your data to a Stored Procedure, then on the server the Stored Procedure would use a SQL Function to split the data and create a temporary table with it, then the stored procedure would continue doing the checking and insert if necessary.

Answer 2

A couple of reasons not to use SQL over the internet.

• One, to me it would pose too much of a risk exposing a sql server on the internet. I do not want SQL attacks :). If you vpn the traffic, then it's a different story since man in the middle attacks are that much harder.
• Two, the SQL protocol is a "chatty" protocol, so a link between servers with high latency will impact your queries a lot.

If possible, I would set up a WCF service on the SQL end, and have my client send the data to the service, which would execute the queries to the database. Any WCF protocol would be less chatty compared to SQL, and you would most likely achieve a speed gain compared to what you are seeing now.

And of course batching the data when transferring it will help reduce the traffic needed to commit the records. You want as few calls over the wire as possible due to latency.

Answer 3

In addition to what Mikael Svenson wrote.

You could overcome the latency by "brute force" and run on multiple db connections. But in general i really recommend Mikael's advice.