CODEWITHSUNDEEP
c#sql-server
ché
ché

Reputation: 277

Advice on options for shared database for distributed c# application

I'd like to know my options for the following scenario:

I have a C# winforms application (developed in VS 2010) distributed to a number of offices within the country. The application communicates with a C# web service which lies on a main server at a separate location and there is one database (SQL Server 2012) at a further location. (All servers run Windows Server 2008)

Head Office (where we are) utilize the same front-end to manage certain information on the database which needs to be readily available to all offices - real-time. At the same time, any data they change needs to be readily available to us at Head Office as we have a real-time dashboard web application that monitors site-wide statistics.

Currently, the users are complaining about the speed at which the application operates. They say it is really slow. We work in a business-critical environment where every minute waiting may mean losing a client.

I have researched the following options, but do not come from a DB background, so not too sure what the best route for my scenario is.

I've currently got a whole lot of the look-up data being cached on start-up of the application but this too takes 2-3 minutes to complete which is just not acceptable!

Does anyone have any ideas?

Upvotes: 3

Views: 809

Answers (1)

Dave Simione
Dave Simione

Reputation: 1441

With everything running through the web service, there is no need for additional SQL Servers to be deployed local to the client. The WS wouldn't be able to communicate with these databases, unless the WS was also deployed locally as well.

Before suggesting any specific improvements, you need to benchmark where your bottlenecks are occurring. What is the latency between the various clients and the web service, and then from the web service and the database? Does the database show any waiting? Once you know the worst case scenario, improve that, and then work your way down.

Some general thoughts, though:

  • Move the WS closer to the database
  • Cache the data at the web service level to save on DB calls
  • Find the expense WS calls, and try to optimize the throughput
  • If the lookup data doesn't change all that often, use a local copy of SQL CE to cache that data, and use the MS Sync Framework to keep the data synchronized to the SQL Server
  • Use SQL CE for everything on the client computer, and use a background process to sync between the client and WS

UPDATE After your comment, two additional thoughts. If your web service payload(s) is/are large, you can try adding compression on the web service (if it hasn't already been implemented).

You can also update your client to do the WS calls asynchronously, either in a thread or if you are using .NET 4.5 using async/await. This would at least allow the client to use the UI, but wouldn't necessary fix any issues with data load times.

Upvotes: 2

Related Questions