Sign in to follow this  
ArchG

Fastest way to retreive data from a MSSQL database from a desktop application

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this