Hello, say I have a desktop application, that needs to get info from a MS SQL database. I'm using C#, on Windows with the .net framework. The only 2 options I know of are.. 1. Connecting directly to the database (sounds kinda dangerous), and using my stored procedure (say usp_attemptLogin) with the System.Data.SqlClient library. Problem with this is, the client is required to connect directly to the database. (some comps don't have correct port open, and this can't be safe) 2. Connecting to a SOAP Web Service, that has a method like

bool WebService.AttemptLogin(string un, string pw)

and then the webservice connects to the database and returns my info to the desktop application. Problem with this, is it seems kinda slow. Especially on slower computers, The first call to the webservice always seems really slow (sometimes like 10s) no matter what the call is. After the initial method call, it speeds up to like 250ms per call. ----- Besides using option 2 and batching more calls together, is there any way I can speed this up? I'm pretty new to this I suppose, how do other apps connect to a database so lightning quick? Or do they normally not even use databases for something like a login? Thanks a lot.

Quote:(some comps don't have correct port open, and this can't be safe

Unless outgoing ports are actively blocked by firewall (rare, mostly in certain enterprise settings), then this isn't an issue.

Opening ports is needed for incoming connections.


But... What is this for?

in the past we made some very quick tools for some basic C.R.U.D operations on a database, with the app connecting straight to the database via port 1433 or whatever it uses.

some of our clients couldn't use the program without their IT guys opening up the ports.

I thought it was normally kinda best practice to have that port turned off or w/e on the server though? and only allow local or trusted connections?

Is that incorrect?

Quote:Original post by ArchG
in the past we made some very quick tools for some basic C.R.U.D operations on a database, with the app connecting straight to the database via port 1433 or whatever it uses.

I'd use a SSH tunnel. Credentials are then handled using that. While it gives you "plain" connection, the traffic is encrypted, and connection authenticated. It would require minimal modification, or perhaps even none - tunnel can be established via third-party tools (see putty, ssh, ...).

This has the benefit of allowing authentication on both sides, and even though accessible from WAN, no resources are directly exposed.

Tunneling approach has the benefit of not requiring any modifications to existing application while still bringing in some degree of security (forgetting the obvious problem of unchecked access to database through application itself).

Quote:some of our clients couldn't use the program without their IT guys opening up the ports.

Yes, hence the previous enterprise setting comment.

There are two organizational issues. One is that some companies simply block everything, the other is that they might only allow HTTP access through corporate proxy (filtering and blocking). SSH ports are often allowed in corporate firewalls.

It's recommended to take that into account.

As for latency - SOAP is fairly bulky, it's as bloated as it gets. If you are using vanilla wrappers it may be that each data access can take multiple round trips, resulting in perceived high latency, even if the network is not a problem.


It's solvable, but it falls under all that "leveraging existing solutions" and "synergy between different infrastructures" type of thing for which a simple technical answer doesn't exist and for which high priced consultants are brought in. The only generic advice I can give is to never *ever* expose database to internet, preferably not even intranet, unless strictly controlled, and that SOAP (as protocol, not how it's used) should not result in such high latency. If the SOAP schema requires multiple sequential calls to certain service, then naive implementation is likely to result in perceived long stalls simply due to round trips.

There's more than one option here. You can use a normal remoting service, WCF over sockets, actually writing to plain sockets, or a REST service, depending on your requirements. There's also JSON rather than XML. Its not going to be as fast as connecting directly to the database, but if you can't connect directly to the database I don't see why you can't choose a method with a lower latency and better data-to-payload ratio.

[Edited by - Rycross on November 6, 2009 7:02:35 PM]